Access update query not working

[Paul-NYS]

Hi


I created two versions of the below Access query to update a field based on matching two fields from the table the update field is in against two columns in another table. I am getting an errors in both instances that prevent me from running it. Any ideas?

Paul


update local
set RegistrationID = oracle.MEDIATOR_ID
from local, oracle
where local.FirstName = oracle.FIRST_NAME and local.LastName = oracle.LAST_NAME;


update local
set RegistrationID =
(select oracle.MEDIATOR_ID from oracle
where local.FirstName = oracle.FIRST_NAME and local.LastName = oracle.LAST_NAME);

[June7]

What is the error message? Might need JOIN clause linking the tables.

[Paul-NYS]

Errors indicated below:

1. UPDATE [local]
SET RegistrationID =
(select oracle.MEDIATOR_ID from oracle
where local.FirstName = oracle.FIRST_NAME and local.LastName = oracle.LAST_NAME);

The error is 'operation must use an updateable query'.

2. update local
set RegistrationID = oracle.MEDIATOR_ID
from local, oracle
where local.FirstName = oracle.FIRST_NAME and local.LastName = oracle.LAST_NAME;

Error is "syntax error (missing operator) in query expression 'oracle.MEDIATOR_ID from local'"

Paul

[June7]

Did you try creating tables join link in the query?

[Paul-NYS]

The original query was set up for 2 tables and did not come with a join (apparently worked that way), so I did not add it and am not totally sure of the syntax, to be honest. I am not at the PC with the database right now, so I cannot attempt it, but where would the join go?

Paul

[June7]

Query in design view, click and hold on key field in one table and drag cursor to key field in other table. Double click on the resulting line to open dialog. Set Jointype.