Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Delete many rows from a table using id in Mysql

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.

like image 235
Kevin Parker Avatar asked Mar 19 '13 15:03

Kevin Parker


People also ask

How do I delete a lot of rows in MySQL?

We can use DELETE statement along with a WHERE clause, which identifies those multiple rows, to delete multiple rows from MySQL table.

How do I delete multiple rows in a table?

If you want to remove more than one row or column, select a cell in each row or column you want to delete. Under Table Tools, click Layout, and then click either Delete Row or Delete Column.

How do I delete multiple rows in phpmyadmin?

There are some scenarios where you have to delete all rows from one table in one go. Click on Check all and then press Delete to do this. If there is a situation where you want to delete only one row from the selected table, then click on the Delete button to remove that particular row.


2 Answers

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; 
like image 86
JoDev Avatar answered Oct 06 '22 08:10

JoDev


how about using IN

DELETE FROM tableName WHERE ID IN (1,2) -- add as many ID as you want. 
like image 26
John Woo Avatar answered Oct 06 '22 08:10

John Woo