How to delete multiple rows in SQL where id = (x to y)

SqlSql Delete

Sql Problem Overview

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

Sql Solutions

Solution 1 - Sql

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;

Solution 2 - Sql

You can use BETWEEN:

DELETE FROM table
where id between 163 and 265

Solution 3 - Sql

Please try this:

DELETE FROM `table` WHERE id >=163 and id<= 265

Solution 4 - Sql

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");

Solution 5 - Sql

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

Solution 6 - Sql

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>
Categories
Recommended Sql Solutions
Recommended Sql Delete Solutions

Previous Solution:

Bash scripting missing ']'

BashScripting

Next Solution:

search by ObjectId in mongodb with pymongo

PythonMongodbPymongo

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questionbalu zappsView Question on Stackoverflow
Solution 1 - SqlBarrankaView Answer on Stackoverflow
Solution 2 - SqlleppieView Answer on Stackoverflow
Solution 3 - SqlKeerthiView Answer on Stackoverflow
Solution 4 - SqlMD SHAYONView Answer on Stackoverflow
Solution 5 - SqlChamin ThilakarathneView Answer on Stackoverflow
Solution 6 - SqlThienPhucView Answer on Stackoverflow