Ms Access gives rubbish when updating a join column

Question

Updating a column that is part of your selection criteria should be no problem I think/thought.
I still have a query that gives odd results:

update table1 as t1 
inner join table2 as t2 on t1.id = t2.old_id
set t1.id = t2.id

I use table2 to map id to old_id.
table1.id and table2.id are both primary keys. table2.old_id is also unique.

It follows that table1.id will still be unique after this update.
WRONG! MS Access will make rumble of this, with some duplicate table.id values.

I think however this is the correct way of updating a column that is used in a join? How could we achieve the desired result in MS Access?

Note: table2 is a mysql view accessed via ODBC.

Answer 1

Using JOINs in UPDATE statements isn't consistently supported, which is why they aren't my habit to use.

UPDATE TABLE1 AS t1
   SET t1.id = (SELECT t2.id
                  FROM TABLE2 t2
                 WHERE t2.old_id = t1.id)

Answer 2

table1.id and table2.id are both primary keys. table2.old_id is also unique.

It follows that table1.id will still be unique after this update.

That ain't necessarily so.

It seems old_ID and (new) ID are of the same data type. A row in table1 that has no matching row in table2 based on the predicate (table1.ID = table2.old_ID) would not be updated. A different row could match a (new) ID value, hence get updated, with the same value as that row that didn't get updated.

Here's an example using Standard SQL (works in SQL Server 2008, not it Access/Jet) which I hope you can follow. Note I've re-written your INNER JOIN using an EXISTS construct to fit the logic of what I am trying to convey:

WITH Table1 (ID) AS
     (
      SELECT ID
        FROM (
              VALUES (1),
                     (2),
                     (3), 
                     (4)
             ) AS Table2 (ID)
     ), 
     Table2 (old_ID, ID) AS
     (
      SELECT old_ID, ID
        FROM (
              VALUES (1, 55),
                     (2, 99),
                     (3, 4)
             ) AS Table2 (old_ID, ID)
     )
-- ID of rows that will not be updated:
SELECT T1.ID
  FROM Table1 AS T1
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM Table2 AS T2
                    WHERE T1.ID = T2.old_ID
                  )
UNION ALL
-- updated IDs
SELECT T2.ID
  FROM Table2 AS T2
 WHERE EXISTS (
               SELECT * 
                 FROM Table1 AS T1
                WHERE T1.ID = T2.old_ID
              );

The resultset:

ID
---
4   --<-- duplicate
55
99
4   --<-- duplicate

In other words, even though all the following are unique:

(table1.ID)
(table2.ID)
(table2.old_ID)

...the following may contain duplicates:

table1.ID
UNION ALL
table2.ID

Answer 3

You said table1.id and table2.id are both primary keys.

But is there any overlap between id values in table1 and table2?

SELECT Count(*) AS num_matches
FROM table1 AS t1 
INNER JOIN table2 AS t2 ON t1.id = t2.id;

If num_matches > 0, then I'm unsure whether Access' database engine will even allow you to change your table1.id values as you wish in a single set-based operation (an UPDATE statement). You might have to open a recordset based on table2 and loop through the rows changing corresponding table1.id values in order to avoid conflicts. Perhaps dropping the PRIMARY KEY constraint from table1.id before making changes would make your task easier.

If num_matches = 0, perhaps you could add a field, new_id, to table1 and use a two step process to avoid changing table1.id while it is being used in the JOIN condition:

UPDATE table1 AS t1 
INNER JOIN table2 AS t2 ON t1.id = t2.old_id
SET t1.new_id = t2.id;

Then

UPDATE table1
SET id = new_id
WHERE new_id IS NOT Null;

I'm not confident that approach would work, either. Again, dropping the PRIMARY KEY constraint from table1.id before the UPDATE may make your task easier.