title: MySQL can’t specify target table for update in FROM multiple table joins
body: <p>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.</p>
  
  <p>This is my Update statement:</p>
  
  <pre><code>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) ;
  </code></pre>
  
  <p>This is a real life update query, not just a simple one table relationship.  </p>
  
  <p>I got round it by creating a temp table, inserting the value, updating the destination table and dropping the temp table.</p>
  
  <p>I would really like to get this right, because it will come up more often. </p>
  
  <p>All assistance is appreciated.</p>
  
  <p>Cheers
  Bernard Bailey</p>