Search code examples
sqlms-access

update table from another table in access

I have two tables in access

table1 (name,nat) 16000 records
table2 (v_name,id) 189000 records

I want to update nat in table1 with id from table2. tried the following

UPDATE table1 SET nat = (SELECT table2.ID FROM table2 INNER JOIN table1 ON table2.V_name = table1.Name);

got the following

error: Operation must use an updateable query

Solution

  • You can try to use UPDATE .... JOIN in access

    UPDATE table1 t1  
INNER JOIN table2 t2
ON t2.V_name = t1.Name
SET t1.nat =  t2.ID