mi***********@gmx.de (Michael) wrote in message news:<e9*************************@posting.google.c om>...
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
Here are some thoughs. In theory there are 3 possibilites in the
relationship of the old and new codes:
1- the old code and the new code are the same - nothing has to be done
2- the old code points to a new code that does not currently exist
I would handle these first
3- the old code points to a new code but the new code is currently in
use
this will require first processing the data with the new code value
If you find an old code that points to a not currently existing new
code and migrate it to the new code then the old code now becomes
available for reuse and when you assign the data to this new value
then this old value now becomes available for reuse.
This bring up another point for item 3 the old code may be on the list
for reuse or it may not.
Using the information and logic above I would create a dependency
chain where A goes into B into C into D to Z and then start processing
at Z driving up through the chain in the reverse order. Each location
freed is then available to the prior location.
Now because every location may not be reused, what you may actually
have is a series of chains rather than one unbroken chain though by
choosing one of the new not currently used values and processing the
location that moves to it you can string the chains together to form
one complete processing order.
Rather than try to build the chain on the fly I would write some plsql
to build the chain and save it in a new column on the old to new
table. I would also consider adding columns to indicate that the
value has been processed for both building the chain and reassigning
the data.
HTH -- Mark D Powell --
