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

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
       
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Guy Hengel [angelIII / a3]

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

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

SQL Server Management Studio suggested this from my first attempt:

UPDATE   Applicants
SET      EMPID =
         (
         SELECT EMPID
         FROM Applicants AS Applicants_1
         WHERE (EMPID IS NOT NULL) AND (keyApplicantID < 200800000)
         )
WHERE     (keyApplicantID > 200800000) AND (SSN = SSN)

...and suggested this from your solution:

UPDATE    Applicants
SET              EMPID =
                          (
                          SELECT     EMPID
                          FROM          Applicants AS Applicants_1
                          WHERE      (EMPID IS NOT NULL) AND (keyApplicantID < 200800000) AND (SSN = SSN)
                           )
WHERE     (keyApplicantID > 200800000)

On both it told me:  "Incorrect syntax near the keyword 'AS'.
                                "Incorrect syntax near the keyword 'WHERE'

I just removed all the aliases and let SMSE put them in how it liked.

Would either way work or would mine (as edited by SMSE) do something horribly bad?

I have seen SQL Server Management Studio Express reject a statement in the past that worked just fine in Enterprise Manager or Query Analyser right on the SQL Server.  
The way that SMSE suggests seems to leave the SSN references ambiguous.  It seems like your statement with a.SSN = b.SSN would be the better statement.
Should I try it in Enterprise Manager dispite SMSE not liking it?




Guy Hengel [angelIII / a3]

the aliases are important, otherwise the joins will not work correctly.

tip: forget the query builder. does mainly crap IMHO.
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

Open in new window

ASKER
David Megnin

Well, here it is as "verified" by SQL Server Enterprise Manager:

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

Microsoft SQL Server Management Studio Express seems to have different rules than SQL Server Enterprise Manager.  Is there another tool that is prefered?  
Your help has saved me hundreds of hours of internet surfing.
fblack61
Guy Hengel [angelIII / a3]

>Is there another tool that is prefered?  
I don't use a tool for writing queries :)

note: you should ALWAYS specify the table alias to qualify the column names. just makes it 100% sure, and later more readable.
ASKER
David Megnin

I see your point and agree.  I wish that the tools that I'm afraid I still must use didn't reject 100% perfect queries.
(You're my SQL hero)  ;-)
ASKER
David Megnin

Thank you!  I wish I could craft a SQL query like that without using a tool!  :-)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
David Megnin

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

This one Said "Returned more than one value" and it was not allowed.
ASKER
David Megnin

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 says "Incorrect syntax near 'AS'"
Guy Hengel [angelIII / a3]

>This one Said "Returned more than one value" and it was not allowed.

that means that the subselect returns more than 1 row.
one solution would be to use TOP 1 (see below), however, you might want to check out if the EMPID are always the same for the same SSN...
UPDATE a
SET EMPID =
      (
       SELECT TOP 1 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

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