带表连接的Mysql更新 - 使用其他表的字段总和更新一个表的字段 [英] Mysql Update with table joins - update one table's field with sum of other table's field

问题描述

我有两个表订单和 Order_Details
Order_Details 表的 order_id 字段作为外键用于订单表的 id_order Orders 表。 / code>表中的价格总和来自 Order_Details 表。

我尝试了以下查询失败： -

pre $ 更新订单，订单详细信息
SET Orders.price_total = sum（Order_Details.price）
WHERE Orders.price_total = 0
GROUP BY Order_Details.id_order


错误 -

 ＃1064  - 您的SQL语法错误;检查与您的MySQL服务器版本相对应的手册，以便在第4行'GROUP BY Order_Details.id_order'附近使用正确的语法
  

如何在一个查询中做到这一点？

谢谢

解决方案您可以将它简化为

 更新订单
 SET Orders.price_total = 
（ 
 SELECT 
 sum（Order_Details.price）
 FROM Order_Details 
 WHERE 
 Orders.id_order = Order_Details.order_id 
）
 WHERE Orders.price_total = 0; 
  

更新用于分组

 更新订单，订单详细信息
 SET订单。价格总计=总金额（订单详细价格）
其中
订单。 price_total = 0 AND 
 Orders.id_order = Order_Details.order_id 
 GROUP BY Order_Details.id_order 
  

I have two tables Orders and Order_Details Order_Details tables's order_id field acts as foreign key to Orders table's id_order table.

I want to update the price_total field of Orders table with summation of prices from Order_Details table.

I tried with the following query but failed:-

Update Orders, Order_Details
  SET  Orders.price_total = sum(Order_Details.price)
 WHERE Orders.price_total=0
 GROUP BY Order_Details.id_order

Error -

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY Order_Details.id_order' at line 4

How to do it in one query?

Thanks

解决方案

You can simplify it to

Update Orders 
SET Orders.price_total = 
(
  SELECT 
    sum(Order_Details.price) 
  FROM Order_Details 
  WHERE 
    Orders.id_order=Order_Details.order_id
)
WHERE Orders.price_total=0;

updated for grouping

Update Orders, Order_Details
  SET Orders.price_total = sum(Order_Details.price)
WHERE 
  Orders.price_total=0 AND
  Orders.id_order=Order_Details.order_id    
GROUP BY Order_Details.id_order

这篇关于带表连接的Mysql更新 - 使用其他表的字段总和更新一个表的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！