Delete statement in a same table

Question

I need to query a delete statement for the same table based on column conditions from the same table for a correlated subquery.

I can't directly run a delete statement and check a condition for the same table in mysql for a correlated subquery.

I want to know whether using temp table will affect mysql's memory/performance?

Any help will be highly appreciated.

Thanks.

Answer 1

You can make mysql do the temp table for you by wrapping your "where" query as an inline from table.

This original query will give you the dreaded "You can't specify target table for update in FROM clause":

DELETE FROM sametable WHERE id IN (     SELECT id FROM sametable WHERE stuff=true ) 

Rewriting it to use inline temp becomes...

DELETE FROM sametable WHERE id IN (  SELECT implicitTemp.id from (SELECT id FROM sametable WHERE stuff=true) implicitTemp ) 

Answer 2

Your question is really not clear, but I would guess you have a correlated subquery and you're having trouble doing a SELECT from the same table that is locked by the DELETE. For instance to delete all but the most recent revision of a document:

DELETE FROM document_revisions d1 WHERE edit_date <    (SELECT MAX(edit_date) FROM document_revisions d2     WHERE d2.document_id = d1.document_id); 

This is a problem for MySQL.

Many examples of these types of problems can be solved using MySQL multi-table delete syntax:

DELETE d1 FROM document_revisions d1 JOIN document_revisions d2    ON d1.document_id = d2.document_id AND d1.edit_date < d2.edit_date; 

But these solutions are best designed on a case-by-case basis, so if you edit your question and be more specific about the problem you're trying to solve, perhaps we can help you.

In other cases you may be right, using a temp table is the simplest solution.