MySQL UPDATE 与来自不同表的 SELECT SUM
我有两张桌子:ITEMS 带有数量和 unit_price (id | name | order_id | qt | unit_price)和表 ORDERS.
I have two tables:
ITEMS with quantities and unit_price (id | name | order_id | qt | unit_price)
and table ORDERS.
我想UPDATE 表orders 并放入orders.total_price sum of multiplication of qt*unit_price 以获取相同订单的总价的订单.
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.
对 items 表的 SELECT 查询非常简单，并且可以很好地为同一 order_id 中的所有项目提供总和:
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
但是我不能在我的 ORDERS 表中插入这个值.我无法完成这项工作:
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
它返回子查询返回多于1行"
我发现了一个非常相似的问题 此处 但答案对我也不起作用:
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)
推荐答案
你可以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]
相关文章