Link to home
Start Free TrialLog in
Avatar of mrosier
mrosierFlag for United States of America

asked on

how to write this update query in Access 2010

Hello! I have a table1 with field1 and field2 and a table2 with field1. I want to set table1.field2 = 1 where table1.field1 exists in table2.field1. I am getting a syntax error though. Can someone tell me how I would write a query that essentially puts a 1 in field2 where field1 values exist in this other table's field? Thanks
Avatar of Brendt Hess
Brendt Hess
Flag of United States of America image

Can you show the query syntax you attempted?
Avatar of mrosier

ASKER

sure, I had said:

update table1 set field2 = 1 where field1 in (select field1 from table2)
after making copies of Table1, try:

UPDATE Table1
SET Field2 = 1
WHERE Exists (SELECT 1 FROM Table2 WHERE Table2.Field1 = Table1.Field1)
try this

update table1
inner join table2 on table1.field1=table2.field1
set table1.field2=1
ASKER CERTIFIED SOLUTION
Avatar of mrosier
mrosier
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mrosier

ASKER

This was the solution that fit me, though the problem was something I didn't think to mention in the original post because I wasn't aware that was the problem. Is this the appropriate way to mark this question? I put an A because that was the exact problem I had.