MySQL UPDATE with SELECT SUM from another table

Question

I have two tables: ITEMS with values ​​and unit_price (id | name | order_id | qt | unit_price) and the ORDERS table.

I want an UPDATE table ORDERS and put orders.total_price sum of multiplications qt*unit_price in orders.total_price sum of multiplications qt*unit_price for the same orders in order to get the total cost of the order.

The SELECT query in the element table is quite simple and works fine, giving sums for all elements within the same order_id:

 SELECT SUM(items.qt*items.unit_price) from items GROUP by items.order_id 

but I cannot insert this value into the ORDERS table. I could not do this job:

 UPDATE orders, items SET orders.total_price = (SELECT SUM(items.qt*items.unit_price) FROM items GROUP BY items.order_id) WHERE orders.id = items.order_id 

it returns "Subquery returns more than 1 row"

I found a very similar question here , but the answer did not help me:

 UPDATE orders SET orders.t_price = (SELECT SUM(items.qt*items.unit_price) from items WHERE orders.id = items.order_id) 

Answer 1

You can UPDATE with JOIN in two tables:

 UPDATE Orders o INNER JOIN ( SELECT order_id, SUM(qt * unit_price) 'sumu' FROM items GROUP BY order_id ) i ON o.id = i.order_id SET o.total_price = i.sumu [WHERE predicate]