Replace values in table columns with values from another table

Question

I need to migrate some tables to a new schema in a Microsoft Access database through the Microsoft JET DB engine using SQL. Effectively there is an ID column in a master table that serves as a foreign key in several other tables. I need to change this ID column to an autonumber column. Unfortunately there is no way to do this through Microsoft JET.

The solution I came up with is to create a new column which is an autonumber column. Then, I need to go to the other tables and replace the original foreign key ID with the new autonumber ID, like this;

Primary table

New ID   Old ID   Other column
1        7        bla
2        21       bla
3        18       bla

Linked table

PT_ID  Data
7      bla
7      bla
18     bla
21     bla

What is the correct SQL statement to replace values in the linked table with the new ID? (i.e. 7 becomes 1, 21 becomes 2, and 18 becomes 3) It would be great if this was in Microsoft JET syntax.

Answer 1

I managed to figure it out. This is what I came up with

UPDATE [LinkedTable], [PrimaryTable]
SET [LinkedTable].[PT_ID] = [PrimaryTable].[NewID]
WHERE [LinkedTable].PT_ID] = [PrimaryTable].[OldID]

Looks really basic when you see it laid out before you like that :-/