Solved

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

Posted on 2015-02-11
3
55 Views
Last Modified: 2015-02-11
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
Comment
Question by:brgdotnet
  • 2
3 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40604963
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
 
LVL 2

Author Closing Comment

by:brgdotnet
ID: 40604993
Thanks Portlet Paul. I wish I had your SQL Skills.  I am slowly learning. I can't thank you enough for this!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40604996
it's a pleasure (gaining those skills is by practice,practice,practice :)
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now