Mysql update with table join - update of one table field with the sum of another table field

Question

I have two Orders tables and Order_Details Order_Details table order_id field acts as a foreign key in the Orders table id_order table.

I want to update the price_total field of the price_total table with the summation of prices from the Order_Details table.

I tried to execute the following query but could not: -

 Update Orders, Order_Details SET Orders.price_total = sum(Order_Details.price) WHERE Orders.price_total=0 GROUP BY Order_Details.id_order 

Mistake -

 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY Order_Details.id_order' at line 4 

How to do it in one request?

thanks

Answer 1

You can simplify it to

 Update Orders SET Orders.price_total = ( SELECT sum(Order_Details.price) FROM Order_Details WHERE Orders.id_order=Order_Details.order_id ) WHERE Orders.price_total=0; 

updated for grouping

 Update Orders, Order_Details SET Orders.price_total = sum(Order_Details.price) WHERE Orders.price_total=0 AND Orders.id_order=Order_Details.order_id GROUP BY Order_Details.id_order 

Strike>