sql update script

[leanneconnexions]

on a test dataset i have created a tbl (testaddress) containing two fields Client_Ref and AddLocality from another table (CR_Clients). I have updated the addlocality values in CR_CLIENTS to Null and now i am trying to put the addlocality back into CR_CLIENTS using the stored data in TestAddress. I am using the following SQL and getting an error. what am i doing wrong?


UPDATE CR_CLIENTS
SET CR_CLIENTS.addlocality = TESTADDRESS.addlocality
WHERE CR_CLIENTS.Client_Ref = TESTADDRESS.Client_Ref


Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'TestAddress' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'TestAddress' does not match with a table name or alias name used in the query.

 

[gmmastros]

This will update records, so it is ALWAYS a good idea to backup data before performing this type of operation.

UPDATE CR_CLIENTS
SET CR_CLIENTS.addlocality = TESTADDRESS.addlocality
From CR_CLIENTS
     Inner Join TESTADDRESS 
       On CR_CLIENTS.Client_Ref = TESTADDRESS.Client_Ref

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[leanneconnexions]

thats great. thanks very much!

 

[gmmastros]

You'd be surprised how often this comes up. Especially since Microsoft Access's syntax (to accomplish the same thing) is different.

I'm glad this worked for you.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom