Inventory update problem

[Kliot]

I am trying to update a master inventory table from an order details table, the query below works fine except when the order details table contains the same code number multiple times. When this occurs the update only updates for the first instance of the code number.

How do I make the update work for all the records not just the unique records?


UPDATE Inventory.Inven tory
SET Qty = Inventory.Inven tory.Qty - Retail.OrderDet ails.Qty FROM Inventory.Inven tory INNER JOIN
Retail.OrderDet ails ON Inventory.Inven tory.Code = Retail.OrderDet ails.Code
WHERE (Retail.OrderDe tails.Invoice = 207070202)

Thanks

[hariharanmca]

I am trying to update a master inventory table from an order details table, the query below works fine except when the order details table contains the same code number multiple times. When this occurs the update only updates for the first instance of the code number.

How do I make the update work for all the records not just the unique records?


UPDATE Inventory.Inven tory
SET Qty = Inventory.Inven tory.Qty - Retail.OrderDet ails.Qty FROM Inventory.Inven tory INNER JOIN
Retail.OrderDet ails ON Inventory.Inven tory.Code = Retail.OrderDet ails.Code
WHERE (Retail.OrderDe tails.Invoice = 207070202)

Thanks

I think it is not possible in SQL Server. But it will work in MS Access.


You have to fetch record and then update it

[shaileshb]

hi

i have gone through ur query, but if possible just send me 1 or two records of each table and tell me exactily what u want

[Kliot]

Here is an example,

Invoice table

Code

[Kliot]

Here is an example,

Invoice table

Code Quantity
DM01 2
LG02 2
DM01 3
QP76 1

The update query will update the Inventory table quantity for DM01 by 2 not 5, the second DM01 is not updated


I can get around this by doing a sum query inside the select but it's not ideal.

UPDATE Inventory.Inven tory
set RQty = Inventory.Inven tory.RQty - od.Quantity
FROM (SELECT Code, SUM(Quantity) AS Quantity FROM Retail.OrderDet ails WHERE Invoice = 207022101
GROUP BY Code) as od WHERE(Inventory .inventory.code = od.code)