Avatar of David Megnin
David MegninFlag for United States of America 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?
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
David Megnin

8/22/2022 - Mon
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

ASKER
David Megnin

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?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
David Megnin

Column or expression 'EMPID' cannot be updated.
ASKER
David Megnin

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
David Megnin

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
David Megnin

"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.
ASKER
David Megnin

Would duplicate SSNs prevent this from working?

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

I get that crap all the time.
ASKER
David Megnin

I'm desperate.  How about this?

In Excel:
=VLOOKUP(D2, A2:BK2,26,FALSE)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
rickchild

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
David Megnin

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?
ASKER
David Megnin

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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
David Megnin

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!"  ;-)