Mysql Update with table joins - 用其他表字段的总和更新一个表的字段

发布于 2024-11-14 09:18:16 字数 723 浏览 6 评论 0原文

我有两个表 Orders 和 Order_Details Order_Details 表的 order_id 字段充当 Orders 表的 id_order 表的外键。

我想使用 Order_Details 表中的价格总和来更新 Orders 表的 price_total 字段。

我尝试使用以下查询但失败：-

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

错误 -

#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

如何在一个查询中执行此操作？

谢谢

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

榕城若虚 2024-11-21 09:18:16

您可以将其简化为

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;

~~更新分组~~

~~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~~

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~~

回复收藏 0 原文

~没有更多了~