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', '0'),
  ('4', '1234', '0'),
  ('6', '1234', '0'),
  ('9', '5678', '0');
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 t1 
    INNER JOIN oc_product t2 
         ON t1.product_id = t2.product_id
SET t1.stock = t2.quantity
WHERE t1.id = (SELECT MAX(id) FROM (SELECT * FROM oc_stock_hpp) o1 WHERE product_id = t2.product_id)
SELECT * FROM oc_stock_hpp


id
product_id
stock


2
1234
0

3
5678
0

4
1234
0

6
1234
7

9
5678
9