troubleshooting Question

How to UPDATE a set of records from the values in the same field of other records with matching values

Avatar of megnin
megninFlag for United States of America asked on
Microsoft SQL ServerSQL
10 Comments1 Solution260 ViewsLast Modified:
I have one table of "Applicants" that spans two years.  
Of last years "Applicants" about 50% were hired and have and employee ID ("EMPID" is not a key).  
This year's Applicants are in the same table.  I can tell which year is which by the 9 digit "keyApplicantID" (PK); this year's start with 2008xxxxx.

I need to update this year's applicants EMPID if we hired them last year so they will have the same EMPID this year that they did last year.  keyApplicantID will be different and unique for every record.  I can match last years record to this years on the SSN field (Social Security Number).

Table:  Applicants
PK:  keyApplicantID
Field1: LastName
Field2: FirstName
Field3: SSN
Field4: EMPID

Is this close?

UPDATE Applicants AS a
SET EMPID =
      (
       SELECT EMPID
       FROM Applicants AS b
       WHERE EMPID IS NOT NULL
       AND keyApplicantID < 200800000
       )
Where keyApplicantID > 200800000
AND a.SSN = b.SSN
       
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 10 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros