phpmyadmin Question posted in
The official documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    You can make a subquery for your SUM

    UPDATE products INNER JOIN sales ON sales.Itemcode = products.Code 
SET products.UnitsSold = (SELECT SUM(sales.SoldQuantity) WHERE sales.ItemCode = products.Code)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search