Update From Another Table Problem 1

[Kliot]

Hi,

I'm having trouble with an update query, I'm trying to update, an inventory table with records from an invoice table. I have the query below that updates the inventory quantity field and it works fine. The problem I have is when the invoice table has multiple items with the same code number the query doesn't update the total number it only updates for one record. How can I correct this problem?

UPDATE Inventory.Inventory
SET WQty = Inventory.Inventory.WQty + Wholesale.OrderDetails.Ship
FROM Wholesale.OrderDetails left outer JOIN
Inventory.Inventory ON Wholesale.OrderDetails.Code = Inventory.Inventory.Code
WHERE (Wholesale.OrderDetails.Invoice = @invoice)

Thanks
Perrin

 

[RiverGuy]

I'm not 100% sure what you are after, so try this on a test copy of your table.

UPDATE a
SET WQty = WQty + b.TotalQty
FROM Inventory.Inventory a
INNER JOIN
  (SELECT Code, SUM(Ship) AS TotalQty
   FROM Wholesale.OrderDetails
   GROUP BY Code) b
ON a.Code = b.Code
WHERE b.Invoice = @invoice

 

[Kliot]

RG,

Thanks that's what I was looking for, I thuoght I might have to do summary first but I wasn't sure.

Perrin