'Updating foreign keys from data table to keys from new table? Without dozens of queries?

I am migrating some data. I have a table data which has quite a lot of columns to reference other tables. And those other tables are being changed and simplified into new tables.

Those new tables have the old keys for migration purposes saved. I now want to update the current keys in the data table with the new keys.

data  { datastuff..., ref1, ref2, ref3,..., ref12 }
data2 { otherdatastuff, ref }
oldtable  { ref, .. }
oldtable2 { ref, .. } ....
oldtable6 { ref, .. } ....

newtable { newKey, data, oldref }

The ref column in data does not link to a fixed table, ref1 can link to oldtable, oldtable2, oldtable6, etc.

The oldref in newtable also links to data2.

I have already migrated the data from the oldtables into the newtable and now I would like to update the keys in data to the newKey in newtable.

I have done the following (which works):

update data 
set ref1 = (select n.newkey from newtable n 
            where data.ref1 = n.oldkey),
set ref2 = (select n.newkey from newtable n 
            where data.ref2 = n.oldkey),
set ref3 = (select n.newkey from newtable n 
            where data.ref3 = n.oldkey)...;

etc.

I just copied this twelve times. It´s not hard, I did the same for table data2.

My question is: could I do that somehow easier without all this copied code?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source