Update foreign keys in backup table

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 2008 Forums

Other SQL Server 2008 Topics

Update foreign keys in backup table

Author

Topic

mc_w
Starting Member

1 Post

Posted - 2011-06-27 : 05:41:39

Hi all

I'm looking for a query to update a column in a backup table PERSON_OLD that contains foreign keys (codes). These keys have been changed in the new version of the database.

The code reference table has been backuped too - so I have the current codes and the old ones. The code tables (eg. CODE_OLD and CODE) have following format:

code bigint,
description varchar(100)

Both tables can be joined using the field "description". The table PERSON_OLD that should be adjusted has following format:

key bigint,
name varchar(100),
address varchar(100),
code bigint

How can I update the "code"-field to actual values?

Thank you very much for any help!

Kind regards
Marc

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-27 : 05:50:50

Put a unique index on description in both tables to make sure that you have a 1-1 mapping.
Then the code mapping will be

select oldcode = o.Code, newcode = n.Code
from Code_Old o
join code n
on o.description = n.description

Now it is just a matter of the update

with cte as
(
select oldcode = o.Code, newcode = n.Code
from Code_Old o
join code n
on o.description = n.description
)
update Person_Old
set code = t2.newcode
from Person_Old p
join cte t2
on p.code = t2.code_old

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources