85

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.

John Woo
  • 258,903
  • 69
  • 498
  • 492
Kevin Parker
  • 1,350
  • 3
  • 14
  • 18

11 Answers11

221

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;
JoDev
  • 6,633
  • 1
  • 22
  • 37
  • Range (`>` and `<`) is better than `IN` in terms of performance as we discovered in our application today. Use the explain statement and see how `IN` does not use indexes. – 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
9

how about using IN

DELETE FROM tableName
WHERE ID IN (1,2) -- add as many ID as you want.
John Woo
  • 258,903
  • 69
  • 498
  • 492
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.

Ghigo
  • 2,312
  • 1
  • 18
  • 19
6

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.

Vivek Todi
  • 361
  • 1
  • 9
  • 24
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
);
cowls
  • 24,013
  • 8
  • 48
  • 78
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.

Boris the Spider
  • 59,842
  • 6
  • 106
  • 166
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));
Muhammad Hani
  • 8,476
  • 5
  • 29
  • 44
2
DELETE FROM table_name WHERE id BETWEEN 1 AND 256;

Try This.

sameer kumar
  • 149
  • 2
  • 3
  • 13
2

Hope it helps: 

DELETE FROM tablename 
WHERE tablename.id = ANY (SELECT id FROM tablename WHERE id = id);
  • 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
0
DELETE FROM tablename WHERE id > 0;
OR
DELETE FROM tablename WHERE id <255;

It deletes id from 1 to 254

Simas Joneliunas
  • 2,890
  • 20
  • 28
  • 35
Phạm Thanh Tùng
  • 9
  • 1
0

All responses are good but have one big wrong assumption that id is a number type. You must know column type in where deleting is performing. If this is a CHAR TYPE or similar string, You must ADD " like this:

DELETE from tablename WHERE id IN ("str1", "str2", "text",..., "string" );

In other case query will not perform and will return false.

Daro from Poland
  • 29
  • 8