CODEWITHSUNDEEP
t-sqlsql-update
GJGerson
GJGerson

Reputation: 211

Updating with Nested Select Statements

I have a table that holds 3 fields of data: Acct#, YMCode, and EmployeeID. The YMCode is an Int that is formatted 201308, 201307, etc. For each Acct#, I need to select the EmployeedID used for the YMCode 201308 and then update all of the other YMCodes for the Acct# to the EmployeedID used in 201308.

so for each customer account in the table...

Update MyTable
Set EmployeeID = EmployeeID used in YMCode 201308

Having a hard time with it.

Upvotes: 0

Views: 842

Answers (1)

mr.Reband
mr.Reband

Reputation: 2430

Put it in a transaction and look at the results before committing, but I think this is what you want: 

UPDATE b
SET EmployeeID = a.EmployeeID
FROM MyTable a
INNER JOIN MyTable b
ON a.[Acct#] = b.[Acct#]
where a.YMCode = 
(SELECT MAX(YMCode) from MyTable)

To get max YMCode, just add select statement at the end.

Upvotes: 2

Related Questions