How do I replace old IDs with new ones?

  • Thread starter Thread starter Frank Martin
  • Start date Start date
F

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
 
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
 
Thank you. I will try.
Regards

Gerald Stanley said:
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
Click to expand...
 
Back
Top