Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 62
  • 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.



Table_1
SSN     PID     FNAME   LNAME
1         NULL   Grattis Maxis
2         NULL   Kerry   Tony
3         NULL   Kerry   Tony
4         NULL   Jerome  Slipy


Table_2

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.
Scripted.txt
0
brgdotnet
Asked:
brgdotnet
  • 2
1 Solution
 
PortletPaulCommented:
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
;

select
*
from table_1
;

Open in new window


Result:
| 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!!!
0
 
brgdotnetAuthor Commented:
Thanks Portlet Paul. I wish I had your SQL Skills.  I am slowly learning. I can't thank you enough for this!
0
 
PortletPaulCommented:
it's a pleasure (gaining those skills is by practice,practice,practice :)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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