TrayS TrayS - 5 months ago 39
SQL Question

SQL Server : update column with value from same table based on choice of values

I have a table that contains inventory items, where the same item can be stored in multiple places (i.e. Total qty=3, 1 stored in cabinet, and 2 in the fridge, and 0 in the pantry). Each of these have an entry type of "20".

There is also rows with entry type of "10" that control the totals. So, in my program I see "Available" as Entry Type "10", and can view each locations quantities by viewing the Entry Type "20".

After running some updates, I noticed that it took my "10" types to zero even if there were items in the "20" rows (on a certain set of items, which I have a list of to include within an "IN" statement to change only those rows)

I am looking to update that table with some sort of CASE statement (I think) to force "10" entry to be equal to the SUM of the "20" entries.

Table looks like this:

ITEM_ID ENTRYTYPE QTY LOCATION_ID
------------------------------------------------
A01 10 0 ALL-AVAILABLE <--THIS ROW SHOULD SHOW QTY=3
A01 20 1 CABINET
A01 20 2 FRIDGE
A01 20 0 PANTRY
A02 10 0 ALL-AVAILABLE <--THIS ROW SHOULD SHOW QTY=6
A02 20 2 CABINET
A02 20 2 FRIDGE
A02 20 2 PANTRY


I think the code is basically something like:

UPDATE Inventory
SET qty = SUM(t2.qty)
FROM Inventory t2
WHERE (ENTRYTYPE = 10 AND qty = 0)
AND ITEM_ID IN (list already generated to be pasted later);


Of course, I couldn't make this work for whatever reason. All help appreciated!

TIA

vkp vkp
Answer

You can use a correlated update.

UPDATE t2
SET qty = (select SUM(qty) from Inventory where item_id=t2.item_id and ENTRYTYPE = 20)
FROM Inventory t2
where ENTRYTYPE=10 AND qty=0

To restrict the item_id's that get updated, use a where condition.

Comments