MySQL can’t specify target table for update in FROM multiple table joins

Question

I have searched for days on how to get around this error while trying to update a field from a multiple join table, with a minimum date from the same mutiple join tableset.

This is my Update statement:

update vtiger_projectmilestone 
Inner Join vtiger_projectmilestonecf  ON vtiger_projectmilestone.projectmilestoneid = vtiger_projectmilestonecf.projectmilestoneid
Inner Join vtiger_crmentity ON vtiger_projectmilestone.projectmilestoneid = vtcrmm.crmid
inner join vtiger_project on vtiger_project.projectid = vtiger_projectmilestone.projectid
Inner Join vtiger_crmentity vtcrmp ON vtcrmp.crmid = vtiger_project.projectid

 set vtiger_projectmilestone.projectmilestonedate =
(select min(vtiger_projecttaskcf.cf_779) 
FROM vtiger_projecttask tvpt
Inner Join vtiger_projecttaskcf tvptcf ON tvpt.projecttaskid  = tvptcf.projecttaskid
Inner Join vtiger_projectmilestone tvpm ON tvpm.projectmilestoneid = tvpt.projecttasknumber
Inner Join vtiger_projectmilestonecf vtpmcf ON tvpm.projectmilestoneid = tvpmcf.projectmilestoneid
Inner Join vtiger_crmentity AS vtcrmm ON tvpm.projectmilestoneid = vtcrmm.crmid
Inner Join vtiger_crmentity AS vtcrmt ON tvpt.projecttaskid = vtcrmt.crmid
where tvpm.projectmilestone_no = vtiger_projectmilestone.projectmilestone_no
) 
where vtiger_projectmilestone.projectid = 
(select vtiger_project.projectid from vtiger_project 
INNER JOIN vtiger_crmentity vtcrmp ON vtiger_project.projectid = vtcrmp.crmid
where vtcrmp.deleted = 0 order by vtiger_project.projectid desc limit 1)
and vtcrmp.deleted = 0
and vtcrmm.deleted = 0 
and (vtiger_projectmilestone.projectmilestonedate is null or      vtiger_projectmilestonecf.cf_763 is null) ;

This is a real life update query, not just a simple one table relationship.

I got round it by creating a temp table, inserting the value, updating the destination table and dropping the temp table.

I would really like to get this right, because it will come up more often.

All assistance is appreciated.

Cheers Bernard Bailey

score 0 · Answer 1 · edited May 23 '17 at 12:31

As stated in this answer you can't use the target update table in a subquery, as you can see in your query

SELECT min(vtiger_projecttaskcf.cf_779) 
FROM vtiger_projecttask tvpt
Inner Join vtiger_projecttaskcf tvptcf ON tvpt.projecttaskid  = tvptcf.projecttaskid
Inner Join 
--using target update table in query
vtiger_projectmilestone tvpm ON tvpm.projectmilestoneid = tvpt.projecttasknumber 
Inner Join vtiger_projectmilestonecf vtpmcf ON tvpm.projectmilestoneid = tvpmcf.projectmilestoneid
Inner Join vtiger_crmentity AS vtcrmm ON tvpm.projectmilestoneid = vtcrmm.crmid
Inner Join vtiger_crmentity AS vtcrmt ON tvpt.projecttaskid = vtcrmt.crmid
where tvpm.projectmilestone_no = vtiger_projectmilestone.projectmilestone_no

However i think that a work around is using the data from the table that you're updating, so instead of using joins, you could write some where conditions for example:

SELECT min(vtiger_projecttaskcf.cf_779) 
FROM vtiger_projecttask tvpt
Inner Join vtiger_projecttaskcf tvptcf ON tvpt.projecttaskid  = tvptcf.projecttaskid 
Inner Join vtiger_projectmilestonecf vtpmcf ON tvpm.projectmilestoneid = tvpt.projecttasknumber
Inner Join vtiger_crmentity AS vtcrmm ON tvpt.projecttasknumber = vtcrmm.crmid
Inner Join vtiger_crmentity AS vtcrmt ON tvpt.projecttaskid = vtcrmt.crmid
where tvpm.projectmilestone_no = vtiger_projectmilestone.projectmilestone_no
--using the projectmilestoneid in a where clause
AND tvpt.projecttasknumber=vtiger_projectmilestone.projectmilestoneid

The caveat could be that probably you will get some performance issues, also, as I don't know the full schema, I can't tell if using other tables in the subquery instead of vtiger_projectmilestone will give you the right result

MySQL can’t specify target table for update in FROM multiple table joins

1 Answers1