MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE oc_stock_hpp (`id` INTEGER, `product_id` INTEGER, `stock` INTEGER);
INSERT INTO oc_stock_hpp (`id`, `product_id`, `stock`) VALUES
  ('2', '1234', '0'),
  ('3', '5678', '6'),
  ('4', '1234', '2'),
  ('6', '1234', '3'),
  ('9', '5678', '7');

CREATE TABLE oc_product (`id` INTEGER, `product_id` INTEGER, `quantity` INTEGER);
INSERT INTO oc_product (`id`, `product_id`, `quantity`) VALUES
  ('1', '1234', '7'),
  ('23', '5678', '9');
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;
SELECT * FROM oc_stock_hpp;


id
product_id
stock


2
1234
0

3
5678
6

4
1234
2

6
1234
5

9
5678
3