How can I get DISTINCT/GROUP BY behavior in UPDATE

Question

I get an Error: Error Number: 1242 Subquery returns more than 1 row

Because My UPDATE's subquery produce repeat values. I know DISTINCT and GROUP BY can remove repeat values in SELECT query.How can I fix it on UPDATE query. My query is Here

UPDATE 
    product_stock AS S
LEFT JOIN
    product_purchase_item AS I
ON
     S.product_id=I.product_id AND S.product_size=I.product_size
LEFT JOIN
    product_purchases AS P
ON
     I.product_purchase_item_id=P.product_purchase_item_id  
SET
    S.product_size_quantity=S.product_size_quantity+I.quantity
WHERE
    S.product_id=?
AND
    S.product_size=?

Suppose, If I convert it in SELECT query

SELECT
 S.*
FROM
 product_stock AS S
LEFT JOIN
 product_purchase_item AS I
ON
 S.product_id=I.product_id AND S.product_size=I.product_size
LEFT  JOIN
 product_purchases AS P
ON
 I.product_purchase_item_id=P.product_purchase_item_id

The query return

product_stock_id  product_id   product_size product_quantity

10                 216          1            1 
10                 216          1            1
11                 216          5            1   

But I need(Just a DISTINCT keyword can do this in SELECT query)

product_stock_id  product_id   product_size product_quantity

10                 216          1            1 
11                 216          5            1   

But,how can I retrieve like above data(UNIQUE VALUES) in my UPDATE subquery? is it possible to use DISTINCT keyword or like anything in my UPDATE query?

Answer 1

If the data is truly identical, you can simply use "min()" without group by/distinct

UPDATE 
    product_stock AS S
LEFT JOIN
    product_purchase_item AS I
ON
     S.product_id=I.product_id AND S.product_size=I.product_size
LEFT JOIN
    product_purchases AS P
ON
     I.product_purchase_item_id=P.product_purchase_item_id  
SET
    --   CHANGE HERE ===============================>
    S.product_size_quantity=S.product_size_quantity+MIN(I.quantity)
WHERE
    S.product_id=?
AND
    S.product_size=?

Answer 2

This should do the trick. Was there a reason for the product_purchases table to be included because it seems to be redundant in your query?

UPDATE product_stock SET product_size_quantity = product_size_quantity + I.quantity

from product_stock
INNER JOIN (select product_id, product_size, sum(quantity) from product_purchase_item group by product_id, product_size) AS I
ON product_stock.product_id=I.product_id AND product_stock.product_size=I.product_size

WHERE product_stock.product_id=?
AND product_stock.product_size=?