How to update multiple rows on table with the result of a select subquery in Mysql-CodePudding

I have a table "product_quantity_cart" that has a "price" column in Mysql. I want to update all rows that match certain "id" with the result of a select that uses values from two different tables. In order to do so, I am executing a select subquery inside the update and as a result I am getting error 1224: "subquery returns multiple rows". I have read that a subquery like mine is not the right way to achieve what I want in Mysql, so I would like to ask which is the proper way to do it. My query looks like so:

update product_quantity_cart set price_product =
  (
  select quantity*price from (select * from product_quantity_cart) as p_q_c  inner join product 
    on product.id_product=p_q_c.id_product
        where id_shopping_cart=7
        );
'''
As you can see, I intend to update column price_product in all rows from table product_quantity_cart.

CodePudding user response：

You're getting the error "subquery returns multiple rows" because MySQL is trying to assign the output of the subquery to each row in the table.

In order to fix this error you can directly reference the fields that contribute to the final value of price_product, like this:

UPDATE 
    product_quantity_cart 
SET 
    price_product = price * quantity

CodePudding user response：

I don't know what your tables looks like but I'm just assuming that the product in your inner join product (given above) is your table2 with columns p.id_product and p.quantity and your product_quantity_cart has columns id_product, price_product, c.price, and c.id_shopping_cart

Then the query could be like this;

update product_quantity_cart c JOIN product p ON p.id_product = c.id_product
set c.price_product = c.price * p.quantity
WHERE c.id_shopping_cart=7