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?
delete d
from imageviewer_crreviewerformdata d
inner join imageviewer_crtask t on d.TaskId = t.TaskId
where t.RevProtId in (1)
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));
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.
