MySQL on delete error - Can't specify target table for update in FROM clause

Question

Guys i am trying to delete some records from the database using a nested query:

delete from imageviewer_crreviewerformdata
where imageviewer_crreviewerformdata.FormDataId in
(select distinct imageviewer_crreviewerformdata.FormDataId
from imageviewer_crreviewerformdata,imageviewer_crtask
where imageviewer_crreviewerformdata.TaskId = imageviewer_crtask.TaskId
and imageviewer_crtask.RevProtId in (1));

i get the well known mysql exception. Could someone help to formulate the query?

Answer 1

delete d 
from imageviewer_crreviewerformdata d
inner join imageviewer_crtask t on d.TaskId = t.TaskId
where t.RevProtId in (1)

Answer 2

i think its just telling you to remove the table alias from the query thus

delete from imageviewer_crreviewerformdata
where FormDataId in
(select distinct imageviewer_crreviewerformdata.FormDataId
from imageviewer_crreviewerformdata,imageviewer_crtask
where imageviewer_crreviewerformdata.TaskId = imageviewer_crtask.TaskId
and imageviewer_crtask.RevProtId in (1));

Answer 3

When i need to delete from a table using that table as the source of select, i usually create a temporary table to use as reference for deletion. Maybe its a bit traditional but it works.

You need to create a temporary table, the delete by joining on the temp table, then drop the temp table.

CREATE TEMPORARY TABLE temp.tempdelete AS select distinct
imageviewer_crreviewerformdata.FormDataId
from imageviewer_crreviewerformdata,imageviewer_crtask
where imageviewer_crreviewerformdata.TaskId = imageviewer_crtask.TaskId
and imageviewer_crtask.RevProtId in (1));

Then you can delete

DELETE FROM imageviewer_crreviewerformdata WHERE FormDataId IN (SELECT FormDataId FROM temp.tempdelete) 

Then drop the temp table.

DROP TABLE temp.temptable.

This require that you have a folder named temp. Its also a good solution thath can be used easier in stored procedures.