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)

Solution

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]

Comments

[Milosz]

Thank you. What should [WHERE predicate] say? Without that affects 0 rows and with "WHERE o.id = i.order_id" also affects 0 rows.

[Mahmoud Gamal]

@user1692064 you can use the WHERE clause to supply extra condition to update the column you specified only if this condition were true, note that this clause is optional and write it if you need it. Also there were 0 rows affected because there might be no fields satisfy the join condition which is ON o.id = i.order_id

[Milosz]

I am sorry it was my lack of knowledge that mysql in phpmyadmin ignores in "affected values" those records that have the same value already. I must have had correct values in all records before, playing with several different queries. Thank you.