asked on

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

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?

rickchild

update a 
set
empid = b.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

Open in new window

David Megnin

ASKER

Oh, yes, I see that the a should have be a "b"

rickchild

Yeah I think that was the problem, it was updating from the new record, and not from the old.

Is that working for you now?

David Megnin

ASKER

Column or expression 'EMPID' cannot be updated.

David Megnin

ASKER

How about:

SELECT LastName, FirstName, SSN, EMPID
INTO Applicants_Backup

/* Then... */
UPDATE Applicants as a
SET a.EMPID = b.EMPID
FROM Applicants_Backup as b
WHERE a.SSN = b.SSN and a.keyApplicantID < 200800000 and b.keyApplicantID > 200800000

Can the two queries be put together into one statment if it works?

rickchild

Try this:

update a 
set empid = b.empid
from applicants a 
join 
(
 SELECT distinct ssn, empid FROM applicants 
 WHERE empid IS NOT NULL 
 AND keyapplicantID < 200800000
) AS b 
on a.ssn = b.ssn
where
a.keyapplicantID > 200800000

Open in new window

David Megnin

ASKER

errr, maybe this would work better:
SELECT LastName, FirstName, SSN, EMPID
INTO Applicants_Backup
FROM Applicants
WHERE (keyApplicantID < 200800000) AND (EMPID IS NOT NULL)

And then update Applicants from Applicants_Backup?

rickchild

That example is like your example, but as a SubQuery rather than a temporary table, but temproary tables are also a good way to set up data for joins as you can index etc.

rickchild

This is the alternative if using a temp table

SELECT     LastName, FirstName, SSN, EMPID
INTO       Applicants_Backup
FROM       Applicants
WHERE      (keyApplicantID < 200800000) AND (EMPID IS NOT NULL)
 
UPDATE Applicants
SET a.EMPID = b.EMPID
FROM Applicants as a
JOIN Applicants_Backup as b
ON a.SSN = b.SSN 
WHERE a.keyApplicantID > 200800000

Open in new window

David Megnin

ASKER

"Cannot use the column prefix 'a'. This must match the object in the UPDATE clauses 'Applicants'."
I changed it to:

UPDATE a
SET a.EMPID = b.EMPID
FROM Applicants as a
JOIN Applicants_2007_EMPID as b
ON a.SSN = b.SSN
WHERE a.keyApplicantID > 200800000

and got:
Column or expression 'EMPID' cannot be updated.

David Megnin

ASKER

Would duplicate SSNs prevent this from working?

Smith, John, 123-45-6789
Smith, johN, 123-45-6789

I get that crap all the time.

David Megnin

ASKER

I'm desperate. How about this?

In Excel:
=VLOOKUP(D2, A2:BK2,26,FALSE)

ASKER CERTIFIED SOLUTION

rickchild

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David Megnin

ASKER

I think I'm going to have to execute it on the Server, SMSE doesn't like it. It verifies it and converts it to a CROSS JOIN

... I ran it in SQL Server Enterprise Manager and it worked. I verified that the new records were updated from the 290 old records.

Whew! I've been working on this since about noon yesterday.

Thank you VERY much!!!

Can those be put together into a SubQuery?

David Megnin

ASKER

Thank you again!

rickchild

This should work, but I seem to be making a meal of a simple query today!

update applicants
set empid = b.empid
from applicants a 
join 
(
 SELECT distinct ssn, empid FROM applicants 
 WHERE empid IS NOT NULL 
 AND keyapplicantID < 200800000
) AS b 
on a.ssn = b.ssn
where
a.keyapplicantID > 200800000

Open in new window

David Megnin

ASKER

Thanks again. Your solution did the trick. Yeah, this turned out to be much trickier than I had anticipated. I told the department head, "Yeah, I can just pull the old EMPIDs into the new records, NO PROBLEM!" ;-)