mysql - 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
)