Update from one table to another. Probably easy.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update from one table to another. Probably easy.

Author

Topic

stmoo
Starting Member

5 Posts

Posted - 2009-02-26 : 02:31:03

Hi, guys.

I'm new so hello all, and go easy on me!

I'm currently struggling with what should, in my opinion, be a fairly simple query.

Take the following tables:


Old
ID    FNAME    LNAME     NINO
===============================
1     BOB      SMITH     ABC123
2     JIM      JONES     XYZ876
3     PETER    HUNTER    LKJ098


New
ID    FNAME    LNAME     NINO     OLDID
=======================================
1     BOB      SMITH     ABC123   NULL
2     JIM      JONES     XYZ876   NULL
3     PETER    HUNTER    LKJ098   NULL

Now, what I want to do is update the OLDID in the new table with the ID that matches on the three other fields.

1) The Fname, Lname AND NINO must match.
2) There will be a number of records in the old table that won't match
3) There may be duplicate records in the old system. These are to be ignored.

Your advice would be greatly appreciated as this one has had me for a good few hours!

Thanks in advance.

S>

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-26 : 02:51:50

check this is this u want


declare @Old table (ID  int,  FNAME  varchar(32),   LNAME varchar(32),  NINO  varchar(32))
insert into @old select 
1  ,   'BOB'  ,    'SMITH' ,    'ABC123' union all select
2   ,  'JIM'   ,   'JONES' ,    'XYZ876' union all select
4   ,  'JIM'   ,   'JONES' ,    'XYZ876' union all select
5   ,  'sachin'   , 'sehwag' ,  'XYZ876' union all select
6   ,  'dhoni'   , 'yuvaraj' ,  'XYZ876' union all select
3   ,  'PETER' ,   'HUNTER',    'LKJ098'


declare @new table (ID  int,  FNAME  varchar(32),   LNAME varchar(32),  NINO  varchar(32), oldid int)
insert into @new select 
1  ,   'BOB'  ,    'SMITH' ,    'ABC123', null union all select
2   ,  'JIM'   ,   'JONES' ,    'XYZ876',null union all select
6   ,  'dhoni'   , 'yuvaraj' ,  'XYZ876',null union all select
3   ,  'PETER' ,   'HUNTER',    'LKJ098',null


update n 
set oldid = s.id
from 
@new n
inner join 
( select * from 
( select row_number() over(partition by id,fname,lname order by id)as rid, * from @old)t 
where rid = 1)s
on n.id = s.id and n.fname = s.fname and n.lname = s.lname

select * from @new

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-26 : 03:18:13

I'm not sure I understand the business about duplicate record in the old system. Does that mean there may be duplicate of FNAME, LNAME and NINO in the Old table? With different ID's? Or duplicate across the entire record, ID, FNAME, LNAME and NINO?

Assuming you may have duplicate people with DIFFERENT IDs in the old system, which ID do you want to associate with the New table? I'm assuming in this case you would make a rule like associate the min(ID) from the Old table with each record in the New table.

If so, the idea here is to first reduce the Old table to only the rows you want, which would be only the minimum value for ID per FNAME, LNAME, NINO. That's what is inside the () definition of the second table in the join. Then you have a "distinct" row set from the Old table, to which you can join the New table on the 3 columns that you want to match: FNAME, LNAME, NINO.

So you can do these select statements to visualize the path your data is taking:

-- this is your dataset from the Old table
select min(id) as id, fname, lname, nino
from Old
group by fname, lname, nino

-- join that with the New table to see which rows you are going to update
select o.id, n.fname, n.lname
from New n
join (select fname, lname, nino, min(id) as id from Old) o
on n.fname = o.fname and n.lname = o.lname and n.nino = o.nino

-- then just add the update part
update n
set n.oldid = o.id
from New n
join (select fname, lname, nino, min(id) as id from Old) o
on n.fname = o.fname and n.lname = o.lname and n.nino = o.nino

Hope this helps you understand how to get there.

stmoo
Starting Member

5 Posts

Posted - 2009-02-26 : 03:29:17

Thanks to both posters for your swift responses.

The duplicates will be duplicate FName, Lname annd NINO but with DIFFERENT ids. Thankfully!

bklr, I tried your query but it affected zero rows.
SFG, I will try to construct a query based on your info.

Thanks again, guys.

S>

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-26 : 03:36:30

I did include the fully constructed update query as the last step, unless you need to alter it for your real table/column names.

G'night

stmoo
Starting Member

5 Posts

Posted - 2009-02-26 : 03:37:59

I feel like I'm getting there now, and understand how the query is constructed.

My query is now (with correct table names):


update tblUser
set tblUser.LegacyApplicantID = OCB1StagingDB2..tblApplicants.Applicantid
from tblUser
join (select fname, lname, ninumber, min(Applicantid) as id from OCB1StagingDB2..tblApplicants group by fname, lname, ninumber) o
on tblUser.NameFirst collate database_default = o.fname and 
   tblUser.NameLast collate database_default = o.lname and 
   tblUser.nino collate database_default = o.ninumber

But now I get the error:


The multi-part identifier "OCB1StagingDB2..tblApplicants.Applicantid" could not be bound

stmoo
Starting Member

5 Posts

Posted - 2009-02-26 : 03:39:13

quote:
Originally posted by SQLforGirls

I did include the fully constructed update query as the last step, unless you need to alter it for your real table/column names.

I saw that after I posted!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-26 : 03:48:50

quote:

Originally posted by stmoo

I feel like I'm getting there now, and understand how the query is constructed.

My query is now (with correct table names):


update tblUser
set tblUser.LegacyApplicantID = OCB1StagingDB2..tblApplicants.Applicantido.id
from tblUser
join (select fname, lname, ninumber, min(Applicantid) as id from OCB1StagingDB2..tblApplicants group by fname, lname, ninumber) o
on tblUser.NameFirst collate database_default = o.fname and 
   tblUser.NameLast collate database_default = o.lname and 
   tblUser.nino collate database_default = o.ninumber

But now I get the error:


The multi-part identifier "OCB1StagingDB2..tblApplicants.Applicantid" could not be bound

stmoo
Starting Member

5 Posts

Posted - 2009-02-26 : 04:03:29

Excellent. thanks!

Seems like that worked. Many thanks guys.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-26 : 04:19:33

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources