Any way you look @ it you're going to have to hit each record. Assuming there is an index on the customer number in each of the tables this will hit work nicely.
Igore the ... they are for indenting only.
use ExitingTable order customernumber
sele 0
use ReplaceMentTable
scan
...select ExistingTable
...do while seek(ReplacementTable.OldCustomerNumber)
......*
......* Replace all instances of the customer number.
......*
......replace CustomerNumber with ReplacementTable.NewCustomerNumber
...enddo
...select ReplacementTable
endscan
Now having said that, it may not be the most efficient way to do it. When we replace the customer number we are likely updating at least one index. This hits multiple files. You may consider removing all the indexes on customer number then running the above "backward"
*
* Make a tag on
*
use ReplaceMentTable order OldCustomerNumber
sele 0
use ExitingTable
scan
...select ReplacementTable
...if seek(ExistingTable.CustomerNumber)
...replace CustomerNumber with ReplacementTable.NewCustomerNumber
...endif
...select ExistingTable
endscan
Best of Luck
>I have been given a conversion file of customer numbers. Follows the typical old_cust, new_cust format. Every customer number on every DBF will have to be renumbered. There are 12 tables. Some sites will have tables that are 32mg with 250,000 records. Each record contains a customer number.
>
>Looking for an efficiency way to renumber. I am hoping there might be a better way than reading the conv_cust file in a loop and taking each record, and doing a REPLACE WHERE on each of the 12 tables. Not terrible, just looking for maybe a better way.
>
>Thanks
>
>Brenda