How to Delete Multiple Rows in Sql Where Id = (X to Y)
I Am Trying to Run a Sql Query to Delete Rows with Id's 163 to 265 in a Table I Tried This to Delete Less Number of Rows Delete from `Table` Where Id in (264...
I am trying to run a SQL query to delete rows with id's 163 to 265 in a table
I tried this to delete less number of rows
DELETE FROM `table` WHERE id IN (264, 265)
But when it comes to delete 100's of rows at a time, Is there any query similar to above method I am also trying to use this kind of query but failed to execute it
DELETE FROM `table` WHERE id IN (SELECT * FROM table WHERE id = )
Please tell me the query to do the above action...
6 Answers
If you need to delete based on a list, you can use IN:
DELETE FROM your_table
WHERE id IN (value1, value2, ...);
If you need to delete based on the result of a query, you can also use IN:
DELETE FROM your_table
WHERE id IN (select aColumn from ...);
(Notice that the subquery must return only one column)
If you need to delete based on a range of values, either you use BETWEEN or you use inequalities:
DELETE FROM your_table
WHERE id BETWEEN bottom_value AND top_value;
or
DELETE FROM your_table
WHERE id >= a_value AND id <= another_value;
You can use BETWEEN:
DELETE FROM table
where id between 163 and 265
Please try this:
DELETE FROM `table` WHERE id >=163 and id<= 265
Delete 4 to 7 ids
DELETE FROM cats
WHERE id BETWEEN 4 AND 7;
By name
DELETE FROM cats
WHERE name IN ("Lucy","Stella","Max","Tiger");
SELECT * FROM your_table ORDER BY DESC
Get the range that you want to delete Ex: 3 to 10
DELETE FROM your_table
WHERE id BETWEEN 3 AND 10;
Make sure to add min value fist in BETWEEN Clause
CREATE PROC [dbo].[sp_DELETE_MULTI_ROW]
@CODE XML
,@ERRFLAG CHAR(1) = '0' OUTPUT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DELETE tb_SampleTest
WHERE
CODE IN(
SELECT Item.value('.', 'VARCHAR(20)')
FROM @CODE.nodes('RecordList/ID') AS x(Item)
)
IF @@ROWCOUNT = 0
SET @ERRFLAG = 200
SET NOCOUNT OFF
Get string value delete
<RecordList>
<ID>1</ID>
<ID>2</ID>
</RecordList>
Sarah Jenkins is a veteran tech journalist with over 12 years of experience covering artificial intelligence, mobile innovations, and digital ethics. Her insights have appeared in leading technology publications worldwide.
