CODEWITHSUNDEEP
sqlsql-serversql-server-2012sql-update
TrayS
TrayS

Reputation: 135

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

Upvotes: 0

Views: 6723

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I would do this using an updatable CTE:

with toupdate as (
      select i.*, sum(qty) over (partition by item_id) as sumqty
      from inventory i
     )
update toupdate
    set qty = sumqty
    where entrytype = 20;

Upvotes: 0

Nick
Nick

Reputation: 7451

Assuming that ENTRYTYPE = 10 is always the record you want to have the total quantity updated in:

UPDATE Inventory 
SET qty = (SELECT SUM(qty) FROM Inventory WHERE ENTRYTYPE = 20)
WHERE ENTRYTYPE = 10 AND qty = 0;

Upvotes: 2

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

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.

Upvotes: 3

Related Questions