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

score 121 · Accepted Answer · answered Apr 28 '11 at 10:04

121

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

answered Apr 28 '11 at 10:04

Nicola Cossu

52,276
15
91
96

@Augusto: glad you find it useful. Your answer was right too. :) – Nicola Cossu Apr 28 '11 at 11:50
46

This needs an explanation. – nhaarman Aug 15 '16 at 14:13
In my case Same table with same table subquery it works fine for me. Thanks – Tejas Soni Nov 10 '17 at 06:38
Saved the day for me. Thanks. – Zaid Haider May 09 '20 at 07:24
@nhaarman a detailed explanation: https://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql – fall Jun 30 '21 at 03:29
+10000 to this answer – Alejandro Giraldo Oct 14 '21 at 20:41

Elysiumplain · Answer 2 · 2017-06-26T21:27:56.547

30

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.

edited Jun 26 '17 at 21:27

answered Jan 19 '17 at 22:00

Elysiumplain

617
8
21

1

Very good explanation and makes sense 1000%, thanks ! – Alejandro Giraldo Oct 14 '21 at 20:40

Delete - I can't specify target table?

2 Answers2

Linked