CODEWITHSUNDEEP
mysqlsql
Bluefire
Bluefire

Reputation: 14139

Select and delete entries from database in one go

I want to make a query that SELECTs some entries based on a certain filter, and then DELETEs them.

This would normally be as easy as SELECT * FROM sometable WHERE foo = bar; DELETE * FROM sometable WHERE foo = bar;. However, I am dealing with a very, very large table, and so am very concerned with performance issues. Hence, I want to go over a table only once, and for every row selected, delete that row as well (the above query goes over the table twice: once for SELECT, and once more for DELETE).

Is this possible?

Upvotes: 0

Views: 414

Answers (1)

Tata
Tata

Reputation: 809

If you are dealing with a very large table, I suggest you to retrieve the rows by chunks (use limit) and order by a primary key, and then run the delete by using the primary key in "in clause"

this way u will not lock your others and slow down your server while u are deleting the rows.

run those in a loop in your code while u get 

SELECT * FROM sometable 
    WHERE foo = bar 
    AND  your_primary_key > last_checked_value 
    ORDER BY your_primary_key  limit 100; 


DELETE from sometable 
    WHERE  your_primary_key 
    IN (value1,value2,...,...,value100);

Upvotes: 2

Related Questions