CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
sconnie
sconnie

Reputation: 413

SQL Server : update a table with values from same table based on IDs in a second table

I have a table called STOCK that I want to update that looks like this:

-------------------------
NUMBER           | UNITS
-------------------------
NA13ALPK1010     |  9
NA13ALANA1010    | 11
NA13ALPK1065     |  4
NA13ALANA106     |  5
ON0003    XS     |  1
AT3322    2      |  3

Based on a second table called PKGINV that looks like this:

----------------------------------------
PKGNUMBER        | BOARDNUMBER
----------------------------------------
NA13ALPK1010   | NA13ALANA1010
NA13ALPK1065   | NA13ALANA106

First, I'd like to be able to do a SELECT with some type of join so that I can show UNITS from STOCK for both PKGNUMBER and BOARDNUMBER in PKGINV.

Then, I'd like to update UNITS in STOCK when PKGINV.PKGNUMBER = STOCK.NUMBER with the PKGINV.BOARDNUMBER UNITS from STOCK.

So after the update, NA13ALPK1010 would have UNITS of 11 (the UNITS from NA13ALANA1010) and NA13ALPK1065 would have UNITS of 5 (the UNITS from NA13ALANA106).

Thanks in advance for your help on this!

Upvotes: 0

Views: 97

Answers (2)

Andrew
Andrew

Reputation: 3796

You need to join Stock to PKGINV two times:

Update s
set s.UNITS = s2.UNITS 
from STOCK s
join PKGINV p on s.NUMBER = p.PKGNUMBER
join STOCK s2 on p.BOARDNUMBER = s2.NUMBER

Upvotes: 0

David C Ellis
David C Ellis

Reputation: 138

Your SELECT is simply a matter of joining the STOCK table twice:

SELECT 
PKGNUMBER_Stock.UNITS AS PKGNUMBER_UNITS
,BOARDNUMBER_Stock.UNITS AS BOARDNUMBER_UNITS
FROM 
PKGINV AS pkginv
JOIN STOCK AS PKGNUMBER_Stock ON PKGNUMBER_Stock.NUMBER = pkginv.PKGNUMBER
JOIN STOCK AS BOARDNUMBER_Stock ON BOARDNUMBER_Stock.NUMBER = pkginv.BOARDNUMBER

Likewise, the UPDATE statement should simply match the join set above:

UPDATE PKGNUMBER_Stock
SET PKGNUMBER_Stock.UNITS = BOARDNUMBER_Stock.UNITS
FROM 
PKGINV AS pkginv
JOIN STOCK AS PKGNUMBER_Stock ON PKGNUMBER_Stock.NUMBER = pkginv.PKGNUMBER
JOIN STOCK AS BOARDNUMBER_Stock ON BOARDNUMBER_Stock.NUMBER = pkginv.BOARDNUMBER

Upvotes: 1

Related Questions