更新时间:2023-02-03 07:49:55
我的猜测是,因为您已经向我们展示了简化的架构,所以缺少一些信息,这些信息将确定为什么要为给定的OrderID重复使用VarietyID值.
My guess is that because you have shown us simplified schema, some info is missing that would determine why have the repeated VarietyID values for a given OrderID.
当您有多行时,SQL Server会随意选择其中之一进行更新.
When you have multiple rows, SQL Server will arbritrarily pick one of them for the update.
在这种情况下,您需要先分组
If this is the case, you need to group first
UPDATE V
SET
Stock = Stock - foo.SumQuantity
FROM
tblVariety V
JOIN
(SELECT SUM(Quantity) AS SumQuantity, VarietyID
FROM tblOrderItem
JOIN tblOrder ON tblOrderItem.OrderId = tblOrder.OrderId
WHERE tblOrder.OrderId = 1
GROUP BY VarietyID
) foo ON V.VarietyId = foo.VarietyId
如果不是,那么OrderItems表PK是错误的,因为如果允许重复的OrderID/VarietyID组合(PK应该是OrderID/VarietyID,或者应该限制为唯一)
If not, then the OrderItems table PK is wrong because if allows duplicate OrderID/VarietyID combinations (The PK should be OrderID/VarietyID, or these should be constrained unique)