SQL updating ID column in multiple tables with new values

Question

I have let's say 20 tables, which have chain_id column with values. We have decided to change all chain_id values to new ones (it's not PK/FK). I have a mapping table with chain_id and new_chain_id.

tbl1:

chain_id	some_data
2004	15
2005	20
2006	16
2007	22

tbl2:

chain_id	new_chain_id
2004	9000
2005	9001
2006	9002
2007	9003

tbl1 after update:

chain_id	some_data
9000	15
9001	20
9002	16
9003	22

What I want to do is take all tables which contain chain_id column (tbl1) and replace all values with new_chain_id from mapping (tbl2). What is the fastest and reliable way to do this and get result tbl1 after update for all tables with chain_id in database?

Answer 1

You can update the tables by joining chain_id columns.

Example for tbl1

UPDATE tbl1
SET tbl1.chain_id = tbl2.new_chain_id
FROM tbl1 
JOIN tbl2  ON tbl1.chain_id = tbl2.chain_id;

To include all tables you can first fetch the table names which needs updation from information_schema, I have excluded the mapping table from it.

select table_name
from information_schema.columns
where column_name = 'chain_id'
and table_name <> 'tbl2'; 

And once all table names are fetched, you can loop through to update

declare @table_name nvarchar(128);
declare @sql nvarchar(max);
declare @chain_id_column nvarchar(128) = 'chain_id'; 
declare @mapping_table nvarchar(128) = 'tbl2'; 

create table tables_with_chain_id (table_name nvarchar(128));

insert into tables_with_chain_id (table_name)
select table_name
from information_schema.columns
where column_name = @chain_id_column
and table_name <> @mapping_table; 

declare table_cursor cursor for
select table_name from tables_with_chain_id;

open table_cursor;
fetch next from table_cursor into @table_name;

while @@fetch_status = 0
begin
    set @sql = 'update ' + quotename(@table_name) + ' set ' + quotename(@chain_id_column) + ' = '
             + quotename(@mapping_table) + '.new_chain_id '
             + 'from ' + quotename(@table_name) 
             + ' join ' + quotename(@mapping_table) 
             + ' on ' + quotename(@table_name) + '.' + quotename(@chain_id_column) 
             + ' = ' + quotename(@mapping_table) + '.chain_id;';    
    exec sp_executesql @sql;   
    fetch next from table_cursor into @table_name;
end;
close table_cursor;
-- deallocate table_cursor;
drop table tables_with_chain_id;

Test output for tbl1

chain_id	some_data
9000	15
9001	20
9002	16
9003	22