MS Access update a table with condition joining to another table

Sam OZ
Sam OZ used Ask the Experts™
on
I need to update an MS Access 2003 database  table based on a condition
I tried I tried
update tblA    set Fld1 = B.Fld1   from tblA A inner   join tblB B     on A.Fld2 = B.Fld2    where B.Fld3 not in ( '000' , '001')

Also I tried options from following (It works for oracle. But not for Access)

  https://www.experts-exchange.com/questions/29083921/update-Oracle-11g-table-tblA-based-on-a-condiion-on-tblA-and-tbl2.html
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
The order in Access (ACE) SQL is different:

UPDATE tblA AS A 
INNER JOIN tblB AS B ON A.Fld2 = B.Fld2 
SET A.Fld1 = [B].[Fld1]
WHERE (((B.Fld3) Not In ('000','001')));

Open in new window


But you can create such a query simply be using the Create Query Design wizard.

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