MySQL UPDATE 与来自不同表的 SELECT SUM
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12551364/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL UPDATE with SELECT SUM from different table
提问by Milosz
I have two tables:
ITEMSwith quantities and unit_price (id | name | order_id | qt | unit_price)
and table ORDERS.
我有两个表：
ITEMS带有数量和 unit_price (id | name | order_id | qt | unit_price) 和 table ORDERS。
I want to UPDATEtable ordersand place in orders.total_price sum of multiplications qt*unit_pricefor the same orders to get total price of the order.
我想UPDATE列出orders并放置orders.total_price sum of multiplications qt*unit_price相同的订单以获得订单的总价。
The SELECTquery on the items table is quite simple and works fine giving sums for all items within the same order_id:
该SELECT上的项目表查询很简单，适用于相同的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 ORDERStable. I couldn't make this work:
但我不能在我的ORDERS表中插入这个值。我无法完成这项工作：
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"
它返回 "Subquery returns more than 1 row"
I found a very similar question herebut 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)
回答by Mahmoud Gamal
You can UPDATEwith JOINing the two tables:
您可以UPDATE使用JOIN两个表：
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]
