Hi all

Can anyone help with an update statement please.

What I have is two tables.
Both T1 and T2 have a cname column and nkey column

What I want to do is update all T1.nKey to be T2.nKey where the cName 
columns match.

I currently do a select * into a recordset and loop through to find a 
match which is sllooooowwwww.

I expected something like 

Update t1 set nKey=(select nKey from t2 where t1.cName=t2.cName)

But running this in access gives "must use updateable query" errors.

I have used this in the past with insert queries
ie Insert into t2 select distinct cname from t1

I'd be grateful if anyone could shed any light on this.

Regards
Graham

Found the answer at wideman-one.com

update vehicles right join drivers on 
vehicles.driver_name=drivers.driver_name
set vehicles.driver_record_number=drivers.record_number

(The field names above are the real ones from the app)


> Hi all

> Can anyone help with an update statement please.

> What I have is two tables.
B> oth T1 and T2 have a cname column and nkey column

> What I want to do is update all T1.nKey to be T2.nKey where the cName 
c> olumns match.

> I currently do a select * into a recordset and loop through to find a 
m> atch which is sllooooowwwww.

> I expected something like 

> Update t1 set nKey=(select nKey from t2 where t1.cName=t2.cName)

> But running this in access gives "must use updateable query" errors.

> I have used this in the past with insert queries
i> e Insert into t2 select distinct cname from t1

> I'd be grateful if anyone could shed any light on this.

> Regards
G> raham