'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 |
---|