SQL:按其相关行更新字段？

Question

我有以下模式：

产品

  id: int pk ai
  title: string
  available: int
  left: int

阶

  id: int pk ai
  status: int

OrderItem

  id: int pk ai
  order_id: int fk Order.id
  product_id: int fk Product.id
  amount: int

因此，当购买产品时，就会创建一个订单，其中包含一个或多个订单项。命令的状态指示订单是否已完成。

我需要更新所有产品的“左”字段。它应该通过对具有特定状态的订单(例如，order_items )的所有产品数量进行汇总来计算，并从Products表中的“可用”值中减去结果(也就是说，如果一个产品有10个可用，并且有一个完整的订单用amount=3列出了该产品，那么最终的left应该是7)。

理想情况下，我希望使用一个SQL查询来完成这个任务。

Answer 1

这很简单..。只需做一件事，从表Order_item取金额之和，其中Order表的OrderId有status =2(我想这是完全状态的代码)，好吧，这需要从可用值中减去。以下是对此的查询-

update Product p
set `left` = available - (select sum(amount) 
                          from orderItem oi, `order` o
                         where oi.order_id = o.id
                           and product_id = p.id
                           and o.status = 2
                           )

这将根据您的需要更新产品表。