具有内部联接的SQL Server更新 [英] SQL Server Update with Inner Join

问题描述

我有3张桌子(简体):

I have 3 tables (simplified):

 tblOrder(OrderId INT)  
  tblVariety(VarietyId INT,Stock INT)  
  tblOrderItem(OrderId,VarietyId,Quantity INT)

如果下订单，我将使用以下方法降低库存水平:

If I place an order, I drop the stock level using this:

UPDATE tblVariety  
SET tblVariety.Stock = tblVariety.Stock - tblOrderItem.Quantity  
FROM tblVariety  
INNER JOIN tblOrderItem ON tblVariety.VarietyId = tblOrderItem.VarietyId  
INNER JOIN tblOrder ON tblOrderItem.OrderId = tblOrder.OrderId  
WHERE tblOrder.OrderId = 1

很好，直到tblOrderItem中有两行具有相同的VarietyId且具有相同的OrderId.在这种情况下，只有一行用于库存更新.它似乎在以某种方式在其中执行GROUP BY VarietyId.

All fine, until there are two rows in tblOrderItem with the same VarietyId for the same OrderId. In this case, only one of the rows is used for the stock update. It seems to be doing a GROUP BY VarietyId in there somehow.

谁能阐明一些想法?非常感谢.

Can anyone shed some light? Many thanks.

推荐答案

我的猜测是，因为您已经向我们展示了简化的架构，所以缺少一些信息，这些信息将确定为什么要为给定的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)

这篇关于具有内部联接的SQL Server更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！