Delete - I can't specify target table?

Question

Why this query doesn't work?

DELETE FROM recent_edits  WHERE trackid NOT IN       (SELECT DISTINCT history.trackid       FROM history JOIN recent_edits ON history.trackid=recent_edits.trackid       GROUP BY recent_edits.trackid) 

I get this message : "You can't specify target table "recent_edits" for update in FROM clause

Answer 1

Try in this way

DELETE FROM recent_edits  WHERE trackid NOT IN       (select * from (SELECT DISTINCT history.trackid       FROM history JOIN recent_edits ON history.trackid=recent_edits.trackid       GROUP BY recent_edits.trackid) as t); 

Answer 2

You can't post-process a table which is locked for deletion. using the hack select * from (query) as Nicola states will generate a temporary table instead of direct access.

Edit - make sure that you give ID to the tables you use since it is nested and will require uniqueID for every table.