来自不同表的SELECT SUM的MySQL UPDATE [英] MySQL UPDATE with SELECT SUM from different table

问题描述

我有两个表: ITEMS带有数量和单价(id |名称| order_id | qt |单价) 和表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 multiplications 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

它返回"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)

推荐答案

您可以UPDATE通过JOIN插入两个表:

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]

这篇关于来自不同表的SELECT SUM的MySQL UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！