SQL Server Update with Inner Join

Question

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 

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.

Answer 1

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.

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   

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)