Updating column values in multipe tables

[Jim]

I am looking for suggestions on how to accomplish the following task with the least amount of hand keying that I can get away with.

I have a main table named Office and 4 other tables that have the Office table ID field value in them.
I have been given a new set of ID values that need to replace the values that are currently in the Office table and then update the ID field in the other 4 tables.

I have only thought of 2 solution and I don’t like either one.
1 Add a new column to the Office table and key in the new ID’s then go through the pages that reference the old ID field and change the SQL queries to use the new field.

2 Change the value of the current Office ID field to the new value. Search the other 4 tables for the old value and then update them to the new value.

Anyone got a better Idea?

[Jennifer Mathews]

I guess this depends on how many rows you have to update, but I would write a trigger on the parent table that updates the four child tables. If there are a lot of rows this will save you a lot of time. I don't have a good example handy, but you should be able to get good example of update triggers off of this site or one like it as well as the syntax.

------------
Jim at 5/26/00 3:43:02 PM

I am looking for suggestions on how to accomplish the following task with the least amount of hand keying that I can get away with.

I have a main table named Office and 4 other tables that have the Office table ID field value in them.
I have been given a new set of ID values that need to replace the values that are currently in the Office table and then update the ID field in the other 4 tables.

I have only thought of 2 solution and I don’t like either one.
1 Add a new column to the Office table and key in the new ID’s then go through the pages that reference the old ID field and change the SQL queries to use the new field.

2 Change the value of the current Office ID field to the new value. Search the other 4 tables for the old value and then update them to the new value.

Anyone got a better Idea?