Conversion of Location Codes with Foreign Keys

I have to do a data migration for a client. The following conversion is required.

Task:
- MOVEMENT table with 7,000,000 records, each has a location code

• Location code has changed; I have a mapping table with the old location code and the new code

Issues:
- The conversion should be done in the same table as I do not want to
move the 7,000,000 records from one table to another if not necessary
- The table has a foreign key to a LOCATION master; this means that I
might have to update LOCATION first; however, I cannot just convert the codes in LOCATION as this would trigger foreign key violations in the MOVEMENT table; I also cannot just add the new locations because some new codes might be the same as existing old codes (e.g. the new code 'PDP' for 'Purchasing Department' could be the same as the old 'PDP' for 'Public Relations Department'.

Does anybody know what the best approach would be here?

Michael Received on Thu Apr 29 2004 - 01:10:07 CDT