Delete many rows from a table using id in Mysql

Question

I am a Linux admin with only basic knowledge in Mysql Queries

I want to delete many table entries which are ip address from my table using id,

currently i am using

DELETE from tablename where id=1;
DELETE from tablename where id=2;

but i have to delete 254 entries,so this method is going to take hours,how can i tell mysql to delete rows that i specify,coz i want to skip deleting some entries out of this 254.

Deleting whole table and importing needed entries is not an option.

id is a number so something like `DELETE from tablename where id<254 and id>3;` or whatever would work. — Boris the Spider, Mar 19 '13 at 15:46

JoDev · Accepted Answer · 2013-03-19T15:58:19.840

207

The best way is to use IN statement :

DELETE from tablename WHERE id IN (1,2,3,...,254);

You can also use BETWEEN if you have consecutive IDs :

DELETE from tablename WHERE id BETWEEN 1 AND 254;

You can of course limit for some IDs using other WHERE clause :

DELETE from tablename WHERE id BETWEEN 1 AND 254 AND id<>10;

edited Mar 19 '13 at 15:58

answered Mar 19 '13 at 15:48

JoDev

6,395
1
21
37

Range (`>` and ` – kouton Nov 15 '14 at 05:49
1

Don't worry about how your DBMS use INDEX or not... see http://stackoverflow.com/questions/586381/mysql-not-using-indexes-with-where-in-clause to understand why sometimes using INDEX will be longer than doing a full table scan! – JoDev Feb 02 '15 at 10:12
Thanks you its help – Jean-Marc Amon Feb 28 '18 at 07:16

score 9 · Answer 2 · answered Mar 19 '13 at 15:45

9

how about using IN

DELETE FROM tableName
WHERE ID IN (1,2) -- add as many ID as you want.

answered Mar 19 '13 at 15:45

John Woo

249,283
65
481
481

score 6 · Answer 3 · answered Mar 19 '13 at 15:49

6

if you need to keep only a few rows, consider

DELETE FROM tablename WHERE id NOT IN (5,124,221);

This will keep only some records and discard others.

answered Mar 19 '13 at 15:49

Ghigo

2,242
1
15
19

score 5 · Answer 4 · answered Mar 19 '13 at 15:46

5

Something like this might make it a bit easier, you could obviously use a script to generate this, or even excel

DELETE FROM tablename WHERE id IN (
1,
2,
3,
4,
5,
6
);

answered Mar 19 '13 at 15:46

cowls

23,287
8
47
77

score 4 · Answer 5 · answered Mar 19 '13 at 15:49

4

Others have suggested IN, this is fine. You can also use a range:

DELETE from tablename where id<254 and id>3;

If the ids to delete are contiguous.

answered Mar 19 '13 at 15:49

Boris the Spider

57,395
6
106
161

score 4 · Answer 6 · answered Mar 19 '13 at 15:52

4

If you have some 'condition' in your data to figure out the 254 ids, you could use:

delete from tablename
where id in 
(select id from tablename where <your-condition>)

or simply:

delete from tablename where <your-condition>

Simply hard coding the 254 values of id column would be very tough in any case.

answered Mar 19 '13 at 15:52

Vivek Todi

341
1
9
24

2

like @tom10271 said this should produce "You can't specify target table in FROM clause" in MySQL – grexlort Mar 08 '19 at 15:59

score 3 · Answer 7 · answered Mar 19 '13 at 15:46

3

Use IN Clause

   DELETE from tablename where id IN (1,2);

OR you can merge the use of BETWEEN and NOT IN to decrease the numbers you have to mention.

DELETE from tablename 
where (id BETWEEN 1 AND 255) 
AND (id NOT IN (254));

answered Mar 19 '13 at 15:46

Muhammad Hani

8,113
4
28
44

score 2 · Answer 8 · answered Sep 30 '14 at 10:23

2

DELETE FROM table_name WHERE id BETWEEN 1 AND 256;

Try This.

answered Sep 30 '14 at 10:23

sameer kumar

149
1
3
12

score 2 · Answer 9 · answered Feb 10 '19 at 09:17

2

Hope it helps:

DELETE FROM tablename 
WHERE tablename.id = ANY (SELECT id FROM tablename WHERE id = id);

answered Feb 10 '19 at 09:17

mangrove108

634
1
16
26

For *10.1.37-MariaDB* I got error: "Error SQL (1235): This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery". – yesnik Oct 10 '19 at 07:26

score 0 · Answer 10 · edited Jul 09 '21 at 02:39

0

DELETE FROM tablename WHERE id > 0;
OR
DELETE FROM tablename WHERE id <255;

It deletes id from 1 to 254

edited Jul 09 '21 at 02:39

Simas Joneliunas

2,705
15
25
32

answered Jul 08 '21 at 23:24

Phạm Thanh Tùng

9

Delete many rows from a table using id in Mysql

10 Answers10

Linked