Query using case and update the last record-CodePudding

I have two tables
oc_stock_hpp

id product_id stock
2  1234       0
3  5678       6
4  1234       2
6  1234       3
9  5678       7

oc_product

id product_id quantity
1  1234       7
23 5678       9

My purpose is to update the difference value (between SUM(stock) and quantity) in the stock field table oc_stock_hpp.

If the SUM(stock) > quantity then subtract the difference from the last record of stock
If the SUM(stock) > quantity then add the difference from the last record of stock

so the result will be like this

id product_id stock
2  1234       0
3  5678       6
4  1234       2
6  1234       5
9  5678       3

I have tried this but error

CASE 
WHEN SUM(shpp1.stock) > p.quantity 
    THEN UPDATE oc_stock_hpp shpp1 INNER JOIN oc_product p ON shpp1.product_id = p.product_id 
        SET shpp1.stock = (shpp1.stock - ((SELECT SUM(shpp2.stock) FROM oc_stock_hpp shpp2 LEFT JOIN oc.product p WHERE shpp2.product_id = p.product_id) - p.quantity)
            WHERE shpp1.id = (SELECT MAX(id) FROM (SELECT * FROM oc_stock_hpp) o1 WHERE product_id = p.product_id)
WHEN SUM(shpp1.stock) < p.quantity  
    THEN UPDATE oc_stock_hpp shpp1 INNER JOIN oc_product p ON shpp1.product_id = p.product_id 
        SET shpp1.stock = (shpp1.stock   (p.quantity - (SELECT SUM(shpp2.stock) FROM oc_stock_hpp shpp2 LEFT JOIN oc.product p WHERE shpp2.product_id = p.product_id))
            WHERE shpp1.id = (SELECT MAX(id) FROM (SELECT * FROM oc_stock_hpp) o1 WHERE product_id = p.product_id)
END
FROM oc_product p
LEFT JOIN oc_stock_hpp shpp ON shpp.product_id = p.product_id

CodePudding user response：

Join the 2 tables to a query that returns the last id and total stock for each product:

UPDATE oc_stock_hpp s
INNER JOIN oc_product p ON p.product_id = s.product_id
INNER JOIN (SELECT MAX(id) id, SUM(stock) stock FROM oc_stock_hpp GROUP BY product_id) t ON t.id = s.id
SET s.stock = s.stock   p.quantity - t.stock;

See the demo.