Update using join executes only ones-CodePudding

I have a table with two rows. These rows using same ItemID but different Quantity and RowID.

When I execute the query shown below, it seems that code runs only once:

Here is my query:

Update t1 
Set t1.Stock = t1.Stock   t2.Quantity
From Inventory t1 
Join InventoryTrans t2 On t1.ItemID = t2.ItemID  
Where t2.RowID In (26221, 26222)

My expected result in stock should be 3. But for some reason is 1

CodePudding user response：

You need to aggregate here. Most likely this:

UPDATE I
SET Stock = I.Stock   IT.TotalQuantity
FROM dbo.Inventory I
     CROSS APPLY (SELECT SUM(Quantity) AS TotalQuantity
                  FROM dbo.InventoryTrans ca
                  WHERE ca.ItemID = I.ItemID
                    AND ca.RowID In (26221, 26222)
                  GROUP BY ca.ItemID) IT;

Note I also use meaningful aliases. Don't use aliases it's t1, t2, t3, as they are meaningless and won't be consistent. See Bad Habits to Kick : Using table aliases like (a, b, c) or (t1, t2, t3)