[FIXED] How to update multiple rows on table with the result of a select subquery in Mysql

Issue

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. 

Solution

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

Answered By - fonz

Answer Checked By - Mary Flores (PHPFixing Volunteer)