how to write this update query in Access 2010

mrosier used Ask the Experts™
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
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can you show the query syntax you attempted?


sure, I had said:

update table1 set field2 = 1 where field1 in (select field1 from table2)
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

after making copies of Table1, try:

SET Field2 = 1
WHERE Exists (SELECT 1 FROM Table2 WHERE Table2.Field1 = Table1.Field1)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016

try this

update table1
inner join table2 on table1.field1=table2.field1
set table1.field2=1
I found my problem folks, my original query works. I assigned generic names to my tables and columns (which was probably pretty obvious), and the problem is that one of my field names began with a number. once I enclosed that field in [ ] my query worked. I am truly sorry for wasting your time!


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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial