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
/* Did not work: "Returned more than one value" and it was not allowed.*/
UPDATE a
SET EMPID =
(
SELECT EMPID
FROM Applicants AS b
WHERE b.EMPID IS NOT NULL
AND b.keyApplicantID < 200800000
AND a.SSN = b.SSN
)
FROM Applicants a
Where a.keyApplicantID > 200800000
/* The query below says "Incorrect syntax near 'AS'" */
UPDATE Applicants AS a
SET EMPID =
(
SELECT EMPID
FROM Applicants AS b
WHERE EMPID IS NOT NULL
AND keyApplicantID < 200800000
AND a.SSN = b.SSN
)
Where keyApplicantID > 200800000
/* This one executed and "updated" 290 rows */
update a
set
empid = a.empid
from applicants a
join applicants b on a.ssn = b.ssn
where
b.empid is not null and
b.keyapplicantID < 200800000 and
a.keyapplicantID > 200800000
...but when I looked at the database ALL the Applicants with keyApplicantID > 200800000 still had NULL values in EMPID
There should have been 290 of them with the same EMPID as their record (same SSN) from last year (keyApplicantID < 200800000).
Any ideas what went wrong?
Start Free Trial
