MySQL UPDATE with SELECT SUM from different table

Question

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

I want to UPDATE table orders and place in orders.total_price sum of multiplications qt*unit_price for the same orders to get total price of the order.

The SELECT query on the items table is quite simple and works fine giving sums for all items within the same order_id:

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

but I can't insert this value in my ORDERS table. I couldn't make this work:

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 didn't work for me as well:

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 JOINing the 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]