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, 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>