Search This Blog

Saturday, July 2, 2011

You can't specify target table 'xx' for update in FROM clause

Try to update a column in MySQL which uses SUM and join and hit error "You can't specify target table 'xx' for update in FROM clause "


UPDATE tblstocktake ap SET TotalQuantity =
(
  SELECT SUM(t.Unitinstock)
  FROM tblstocktake t
  JOIN tblstocktake a
  ON a.productid = t.productid
  WHERE a.productid = ap.productid
  GROUP BY a.productid
);

Which is not working.

Finally, found a solution which is working


UPDATE tblstocktake LEFT JOIN
(SELECT productid,
SUM(unitinstock) AS baseVal
FROM tblstocktake
GROUP BY productid)
AS totalSum USING (productid)
SET totalquantity=baseVal;

1 comment:

Info said...

Great!!! solve my problem
Thanks