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

User · Answer

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

User · Answer

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

User · Answer

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 lt  gt 10

User · Answer

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

User · Answer

DELETE FROM table name WHERE id BETWEEN 1 AND 256    Try This

User · Answer

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  lt your-condition gt     or simply   delete from tablename where  lt your-condition gt    Simply hard coding the 254 values of id column would be very tough in any case

User · Answer

how about using IN  DELETE FROM tableName WHERE ID IN  1 2  -- add as many ID as you want

User · Answer

Others have suggested IN  this is fine  You can also use a range   DELETE from tablename where id lt 254 and id gt 3    If the ids to delete are contiguous

User · Answer

Hope it helps    DELETE FROM tablename  WHERE tablename id   ANY  SELECT id FROM tablename WHERE id   id

[mysql] Delete many rows from a table using id in Mysql

Examples related to mysql

Examples related to sql

Examples related to sql-delete