• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 64
  • Last Modified:

I need to join two tables and update the first one.

I need to get the PID value from Table_2, and place it in the PID value of Table_1, based upon a match between the persons first and last name. FNAME and LNAME, in both tables. There are duplicate names in both tables, however that does not matter because I just want to pair each matching record in Table_1 with the first occurrence of the PID value in Table_2. Note that Table_2 contains more records than Table_1.
So the join will just ignore excessive records in Table_2. Can someone help me out with the query. Below is an example.

1         NULL   Grattis Maxis
2         NULL   Kerry   Tony
3         NULL   Kerry   Tony
4         NULL   Jerome  Slipy


PID           FNAME   LNAME
AS4560    Grattis Maxis
AOP558   Kerry   Tony
TRE446    Kerry   Tony
LJ0000      Kerry   Tony
UIOUOI     Kerry   Tony
JKLMNW   Santaro Gleeson
POLTII       Selena  Fargaso
UOUIOI      Mary    Flipshack
AFDKLJ       Jerome  Slippy

Table_1 after the update based upon the two above tables. :

SSN     PID           FNAME   LNAME
1         AS4560    Grattis     Maxis
2         AOP558    Kerry       Tony
3         TRE446     Kerry       Tony
4         AFDKLJ     Jerome     Slipy

Attached are table_1 and table_2 scripted with data.
  • 2
1 Solution
with CTE as (
        select t1.ssn, t2.pid, t1.fname, t1.lname
        from (
              select * , row_number() over(partition by fname, lname order by ssn) as rn
              from table_1
             ) t1
        inner join (
            select * , row_number() over(partition by fname, lname order by (select 1)) as rn
            from table_2
          ) t2 on t1.fname = t2.fname and t1.lname = t2.lname and t1.rn = t2.rn
update table_1
set table_1.pid = CTE.pid
from table_1
inner join CTE on table_1.ssn = CTE.ssn

from table_1

Open in new window

| SSN |    PID |   FNAME | LNAME |
|   1 | AS4560 | Grattis | Maxis |
|   2 | AOP558 |   Kerry |  Tony |
|   3 | TRE446 |   Kerry |  Tony |
|   4 | AFDKLJ |  Jerome | Slipy |

Open in new window

See it at: http://sqlfiddle.com/#!3/773ea/7

Thanks for the DDL and insert script!!!
brgdotnetcontractorAuthor Commented:
Thanks Portlet Paul. I wish I had your SQL Skills.  I am slowly learning. I can't thank you enough for this!
it's a pleasure (gaining those skills is by practice,practice,practice :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now