CODEWITHSUNDEEP
sqlms-access
tylercomp
tylercomp

Reputation: 901

Syntax Error (missing operator) in query expression?

hello and thanks in advance for any help.

I am getting an error: 'Syntax Error (missing operator) in query expression' and I do not know why... any ideas?

This is my query:

 UPDATE 
table1

SET 
table1.country_name = table2.COUNTRY,
table1.city = table2.CITY ,
table1.state = table2.STATE 

FROM
 ( table1 
inner join
table2 
ON
 table2.SITE_ID = table1.SiteID )

where
table1.country_name is null;

Upvotes: 1

Views: 5934

Answers (2)

mwolfe02
mwolfe02

Reputation: 24207

There is no FROM clause in an UPDATE query in Access/Jet SQL:

UPDATE table1 INNER JOIN table2 ON table1.SiteID = table2.SITE_ID
SET table1.country_name = table2.COUNTRY,
    table1.city = table2.CITY ,
    table1.state = table2.STATE 
WHERE table1.country_name is null;

Upvotes: 5

John Hartsock
John Hartsock

Reputation: 86862

Lose the parenthesis around the joined tables. In addition I would personally make an alias for table1 and table2 and reference the table1 alias in the update. Like this:

UPDATE t1
SET 
  t1.country_name = t2.COUNTRY,
  t1.city = t2.CITY,
  t1.state = t2.STATE 
FROM table1 t1 
INNER JOIN table2 t2 ON t2.SITE_ID = t1.SiteID
WHERE
  t1.country_name is null;

Upvotes: 2

Related Questions