MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE oc_stock_hpp (id INT, product_id INT, stock INT);
INSERT INTO oc_stock_hpp VALUES
(2,  1234,       0),
(3,  5678,       0),
(4,  1234,       0),
(6,  1234,       0),
(9,  5678,       0);
SELECT * FROM oc_stock_hpp;

CREATE TABLE oc_product (id INT, product_id INT, quantity INT);
INSERT INTO oc_product VALUES
(1,  1234,       7),
(23, 5678,       9);
SELECT * FROM oc_product;

Records: 5  Duplicates: 0  Warnings: 0




id
product_id
stock


2
1234
0

3
5678
0

4
1234
0

6
1234
0

9
5678
0



Records: 2  Duplicates: 0  Warnings: 0




id
product_id
quantity


1
1234
7

23
5678
9


UPDATE oc_stock_hpp t1                        -- this table copy will be updated
JOIN ( SELECT MAX(id) id, product_id          -- this subquery allows to define
       FROM oc_stock_hpp t2                   -- the most last row ( and this row
       GROUP BY 2 ) t3 USING (id, product_id) -- will be updated only )
JOIN oc_product t4 USING (product_id)         -- this table copy provides new value
SET t1.stock = t4.quantity;                   -- and perform the update

Rows matched: 2  Changed: 2  Warnings: 0


SELECT * FROM oc_stock_hpp;


id
product_id
stock


2
1234
0

3
5678
0

4
1234
0

6
1234
7

9
5678
9