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

Question

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

Possible duplicate of [SQL Delete Records within a specific Range](http://stackoverflow.com/questions/8225036/sql-delete-records-within-a-specific-range) — , Dec 14 '16 at 09:42
This is not a duplicated from the above mentioned...the other relates to deletion in range of consecutives, the question in this refers to deletion of possible non-consecutive id's, a list of ids.. — Merak Marey, Sep 01 '19 at 07:35

score 247 · Answer 1 · edited Oct 23 '19 at 08:06

247

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;

edited Oct 23 '19 at 08:06

Community

1
1

answered Apr 16 '13 at 05:35

Barranka

20,547
13
65
83

3

Thank you! I have used BETWEEN and Its executed successfully to my requirement. – balu zapps Apr 16 '13 at 07:22
10

@baluzapps If you find this answer useful, accept it ;) – Barranka Dec 12 '14 at 15:02
A question related to this: What if I have composite key? – Mayur Nov 13 '18 at 00:24
1

@MayurBeldar: if you have a new question, post it *as a question*, not as a comment – Barranka Nov 13 '18 at 01:10

score 32 · Answer 2 · answered Apr 16 '13 at 05:35

32

You can use BETWEEN:

DELETE FROM table
where id between 163 and 265

answered Apr 16 '13 at 05:35

leppie

115,091
17
196
297

score 6 · Answer 3 · edited Nov 30 '17 at 11:32

6

Please try this:

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

edited Nov 30 '17 at 11:32

Steven

1,996
3
22
33

answered May 13 '16 at 04:34

Keerthi

525
8
14

score 0 · Answer 4 · answered Oct 28 '21 at 10:14

0

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

answered Oct 28 '21 at 10:14

Chamin Thilakarathne

347
1
6
19

score -7 · Answer 5 · edited May 13 '16 at 04:58

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>

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

5 Answers5

Linked

Related