I have two tables

oc_stock_hpp

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

oc_product

id product_id quantity
1  1234       7
23 5678       9

My purpose is to update value in the stock field table oc_stock_hpp where id=6 become 7 and id=9 become 9 as in oc_product table

I have tried

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 oc_stock_hpp GROUP BY product_id)

and got error Subquery returns more than 1 row

CodePudding user response：

You must have as result only 1 id

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

db<>fiddle here

CodePudding user response：

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

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=54b5b671ada99ec44ae24a5353460320

CodePudding user response：

You must make the subquery return one and only one result:

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 oc_stock_hpp GROUP BY product_id ORDER BY t1.id DESC LIMIT 1)

This is the easiest way for me.