Thanks Cindy,

There is of course my normal candidate keys such as order number etc
and it is these keys that I use when I have to carry out the update routines I mentioned earlier.

My question is not so much on how to do the update the old records with the new because I do that already, I was just fishing for a free function:)

It was more along the lines of 'is there an easier way of doing this that I am missing'

My problem is that one of my systems which uses surrogate primary keys sometimes updated by a system that does not have surrogate keys. This is a legacy problem that is likely to stay a problem until I re-write the older system. The older system is really the one that should have been creating the surrogate primary keys in the first place.

At present I have to keep writing routines that update the records in the new system from the old to avoid losing the existing primary key information.

I expect I will have to take the time out to write a function that can impact the changes from one table onto another table.

This might look something like

ModTable(TargetTable,SourceTable,IndexExpresion)

where one table update the other based on a shared specified index expression.

I hope this problem will eventually go away when I change the older system, but I still have the odd bit of doubt about that as well:(





>
>You start out with a Parent table with Parent records and a Child table with child records related to the parent records by your key field.
>
>You want to import a whole new set of parent records. What should happen to the child records in this case? How would the "old children" relate to the "new parents?"
>
>There must be some other field that relates the parents to the children if the "old children" should be able to match with the "new parents."
>
>Can you say more about the old and new parent records and how the children are logically related to them?
>
>
>>In the old days if I wanted to replace an entire set of parent records with a new set from a different source, all I had to do was delete them and append in the new set (with safeguards of course).
>>
>>Now I can no longer do this because my child tables still refer to the parents old primary key (my nextkey() routine having created new keys automatically