Geek Answers Handbook

Change a large number of record keys using a map table

I have a set of records indexed by ID numbers, I need to convert these record indices to a new ID number. I have a table with two columns matching old numbers with new numbers.

For example, given these two tables, what does the update statement look like?

Given:

OLD_TO_NEW oldid | newid ----------------- 1234 0987 7698 5645 ... ...

and

 id | data ---------------- 1234 'yo' 7698 'hey' ... ...

It is required:

 id | data ---------------- 0987 'yo' 5645 'hey' ... ...

This oracle, so I have access to PL / SQL, I'm just trying to avoid it.

+4
source share
5 answers

I would have a unique index on OLD_TO_NEW.oldid and an update in the inline view:

 update (select id, newid from old_to_new, my_table where my_table.id = old_to_new.oldid) set id = newid
+4
source 
 UPDATE base_table SET id = (SELECT newid FROM old_to_new WHERE oldid = id)

What would I do this in MySQL, and I think this is pretty standard.

+2
source

Both update statements from Ramon and David Aldridge should work fine, but based on the number of records being updated, it might be faster to work with the temp table as follows:

  create table temp as ( select newid, data from old_to_new join my_table on my_table.id = old_to_new.oldid);

Then truncate the old table and copy the tempo table to the old table or discard the old table and rename the temporary table. (Note: Add a few additional statements to process entries in which you do not have new values.)

0
source

Here's how to do it in Microsoft SQL Server 2005. I have not had access to the Oracle database for many years, so this may not work for Oracle.

 UPDATE target_table SET id = newid FROM OLD_TO_NEW WHERE target_table.id = OLD_TO_NEW.oldid;

You might want to index OLD_TO_NEW.oldid so that the update connection can work efficiently.

0
source

First back up the database. I will also personally backup the table in the production database if something goes wrong and you need to go back to the old path in a hurry.

Next problem: do you have related tables that also need these identifiers? If not, you can update it using the update instructions. Write your update status so that you can choose and make sure that it will be updated correctly. If you make a lot of records, you can do it in batches of, say, 1000 records at a time. In one situation, you may need to monitor whether the identifier values ​​overlap, direct updating may not work (you run a unique index). In this case, you need to add the column, fill it with new values, then divide the old column and rename the new one. You will also need a script from all indexes, fks, etc., because you will also need to restart them.

Linked tables are getting a lot more complicated, but a new column is the best way to go in this case.

0
source

More articles:


All Articles