Solved

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

Posted on 2015-02-11
3
58 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
rolling count by date, hour query 7 30
Access PS SQLSERVER from powershell 1 29
sql trace 4 27
Sql server query 8 20
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

734 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