UPDATE and SET column row values by calculating values from antoher table

I have two tables:

products – which has UnitsSold(total/cumulative amount of sold products/item), and

sales – which has SoldQuantity (how many units sold per transaction)

The same unit could be sold many times, so we need to calculate how many times it sold from sales table which have SoldQuantity and show the result in UnitsSold, which will show how many items we sold in total per item.

I use this Query but it return some problems

#1111 – Invalid use of group function):

UPDATE products 
 JOIN sales 
   ON sales.Itemcode = products.Code 
  SET products.UnitsSold = SUM(sales.SoldQuantity) 
WHERE sales.ItemCode = products.Code

So which is the correct query to return the accurate results and solve this problem?

Answer

You need to aggregate before joining:

UPDATE products p INNER JOIN
       (SELECT s.ItemCode, SUM(s.SoldQuantity) as SoldQuantity
        FROM sales s
        GROUP BY s.ItemCode
       ) s
       ON s.Itemcode = p.Code
    SET p.UnitsSold = s.SoldQuantity;