How do I replace old IDs with new ones?

[Frank Martin]

I have designed a new address system for an application but for the moment
this is separate from the existing system.

Now I want to replace the 'CustID' primary keys thought the old system with
the 'PeopleID' of the new system so as to retain several years' records.

Can someone advise me on how to start to do this given that the old 'CustID'
is in several linked tables such as:
CustPayments
CustOrders and
CustPrice

Please help, Frank

 

[Gerald Stanley]

I would start by adding a new column 'peopleId' onto every
table that has the column 'custId'. Then I would look at
populating this new column on the table where 'custId' is
the primaryKey (known in the example code below as
masterTable). Once that has been done, the updates to the
other tables is a straightforward
UPDATE childTable INNER JOIN masterTable ON
childTable.custId = masterTable.custId
SET childTable.peopleId = masterTable.peopleId
Only when I am satisfied that the population of the new
'peopleId' is correct on every table would I remove the
'custId' column.

Hope This Helps
Gerald Stanley MCSD

 

[Frank Martin]

Thank you. I will try.
Regards

I would start by adding a new column 'peopleId' onto every
table that has the column 'custId'. Then I would look at
populating this new column on the table where 'custId' is
the primaryKey (known in the example code below as
masterTable). Once that has been done, the updates to the
other tables is a straightforward
UPDATE childTable INNER JOIN masterTable ON
childTable.custId = masterTable.custId
SET childTable.peopleId = masterTable.peopleId
Only when I am satisfied that the population of the new
'peopleId' is correct on every table would I remove the
'custId' column.

Hope This Helps
Gerald Stanley MCSD