Solved

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

Posted on 2015-02-11
3
56 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
export sql results to csv 6 34
Inserting oldest record into new table. 5 21
SQL View nearest date 5 34
transaction in asp.net, sql server 6 30
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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

815 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

11 Experts available now in Live!

Get 1:1 Help Now