Delete many rows from a table using id in Mysql
MysqlSqlSql DeleteMysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
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;
Solution 2 - Mysql
how about using IN
DELETE FROM tableName
WHERE ID IN (1,2) -- add as many ID as you want.
Solution 3 - Mysql
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.
Solution 4 - Mysql
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
);
Solution 5 - Mysql
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.
Solution 6 - Mysql
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.
Solution 7 - Mysql
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));
Solution 8 - Mysql
DELETE FROM table_name WHERE id BETWEEN 1 AND 256;
Try This.
Solution 9 - Mysql
Hope it helps:
DELETE FROM tablename
WHERE tablename.id = ANY (SELECT id FROM tablename WHERE id = id);
Solution 10 - Mysql
DELETE FROM tablename WHERE id > 0;
OR
DELETE FROM tablename WHERE id <255;
It deletes id from 1 to 254