mysql - 子查询在更新集子句中返回超过 1 个值

Question

我有两个表:

1-shipment_order_product(orderid,productid,quantity)
2-stock_product(productid,quantity)

在shipment_order_product表中，一个orderid对应多个productid，这意味着我在一个订单中购买了多个产品。

我想更新我根据特定订单 ID 购买的每个 productid 的 stock_product 数量。

这是我的查询:

update product 
SET NoOfItems= (select sum(b.Quantity) 
from product as a,shippment_order_product as b
where a.Product_ID=b.Product_ID
group by a.Product_ID,b.Product_ID)

这会产生错误:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

有什么解决办法吗？

Answer 1

这是 mssql - 您标记为多个

update p  
SET p.NoOfItems = sum(s.Quantity) 
from product as p 
join shippment_order_product as s
on p.Product_ID = s.Product_ID
group by p.Product_ID