Change Large Number of Record Keys using Map Table

Question

I have a set of records indexed by id numbers, I need to convert these record's indexes to a new id number. I have a two column table mapping the old numbers to the new numbers.

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

Given:

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

and

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

Need:

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

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

Answer 1

UPDATE base_table SET id = (SELECT newid FROM old_to_new WHERE oldid = id)

That's how I would do it in MySQL and I think that's pretty standard.

Answer 2

I'd have a unique index on OLD_TO_NEW.oldid and update on an inline view:

update (select id,
               newid
          from old_to_new,
               my_table
         where my_table.id = old_to_new.oldid)
set id = newid

Answer 3

Both of the update statements from Ramon and David Aldridge should work fine, but based on the number of records to be updated it may be faster to work with a temp table like this:

 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 temp table into the old table or drop the old table and rename the temp table. (Note: Add some extra statements to handle for records where you don't have new values.)

Answer 4

This is how I would do it in Microsoft SQL Server 2005. Haven't had access to an Oracle database in 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 may want to index the OLD_TO_NEW.oldid so the update join can run efficiently.

Answer 5

First make a database backup. I'd also personally make a table backup in a work database in case something goes wrong and you need to get back to the old way in a hurry.

The next concern is do you have related tables that will also need these ids? If no then you can update using an update statement. Write your update statment so that you can do a select and make sure that it will update properly. If you are doing a lot of records, you may want to do this in batches, say 1000 records at a time. ONe situation you may need to watch out for is if the values of the ids overlap a straight update might not work (you'll run into the unique index). In this case you need to add a column, populate it with the new values, then delte the old column and rename the new one. YOu will also need to script out all indexes, fks etc. becasue you will also need to rerun them.

Related tables becomes much more complicated, but the new column is the best way to go inthis case as well.