469,646 Members | 1,567 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,646 developers. It's quick & easy.

Inventory update problem

5
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.Inventory
SET Qty = Inventory.Inventory.Qty - Retail.OrderDetails.Qty FROM Inventory.Inventory INNER JOIN
Retail.OrderDetails ON Inventory.Inventory.Code = Retail.OrderDetails.Code
WHERE (Retail.OrderDetails.Invoice = 207070202)

Thanks
Jul 13 '07 #1
4 1957
hariharanmca
1,977 1GB
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.Inventory
SET Qty = Inventory.Inventory.Qty - Retail.OrderDetails.Qty FROM Inventory.Inventory INNER JOIN
Retail.OrderDetails ON Inventory.Inventory.Code = Retail.OrderDetails.Code
WHERE (Retail.OrderDetails.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
Jul 14 '07 #2
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
Jul 16 '07 #3
Kliot
5
Here is an example,

Invoice table

Code
Jul 16 '07 #4
Kliot
5
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.Inventory
set RQty = Inventory.Inventory.RQty - od.Quantity
FROM (SELECT Code, SUM(Quantity) AS Quantity FROM Retail.OrderDetails WHERE Invoice = 207022101
GROUP BY Code) as od WHERE(Inventory.inventory.code = od.code)
Jul 16 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Oleg | last post: by
2 posts views Thread by Kenneth Courville | last post: by
7 posts views Thread by simon | last post: by
1 post views Thread by Melissa | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.