CODEWITHSUNDEEP
sqlms-access
Sceptical Jule
Sceptical Jule

Reputation: 917

Syntax error (missing operator) when using UPDATE with INNER JOIN

I'm trying to execute this SQL query on an MS Access DB:

UPDATE WW_B
SET WW_B.WWtype_ID=1 
INNER JOIN New_data
  ON WW_B.StdNr = New_data.StdNr;

But I get the following error:

[Microsoft][ODBC Microsoft Access Driver]

Syntax error (missing operator) in expression 1 INNER JOIN New_data on WW_B.StdNr = New_data.StdNr.

I don't see where any operator is needed, since I don't use any parentheses or quotation marks.

I've also tried WWtype_ID='1' and WWtype_ID="1" and got the same error.

What am I doing wrong?

Upvotes: 3

Views: 1406

Answers (3)

dcbeckman
dcbeckman

Reputation: 122

I was having the same problem and found this question while searching for an answer. Luckily I was able to find a solution while digging through some Access queries at work.

This is just another situation where MS Access does not play nice with standard SQL syntax.

The other answers are incorrect. You do not need a FROM clause, Access will just give you the same error message. Where you ran into the error was where you placed the JOIN. It seems intuitive that you would have FROM...JOIN... But this is MS Access where working with SQL is never intuitive.

In Access, UPDATE seems to take the place of FROM. Therefore, you add the JOIN statement to the end of the UPDATE clause.

UPDATE WW_B
INNER JOIN New_data
  ON WW_B.StdNr = New_data.StdNr
SET WW_B.WWtype_ID=1;

You can also add a WHERE clause after the SET statement if desired.

Upvotes: 3

mohkhan
mohkhan

Reputation: 12305

You are missing the FROM clause

UPDATE WW_B SET WW_B.WWtype_ID=1 FROM <your table> INNER JOIN New_data on WW_B.StdNr = New_data.StdNr

Upvotes: -1

Danny Beckett
Danny Beckett

Reputation: 20838

You need a FROM clause when using an INNER JOIN on an UPDATE:

UPDATE WW_B
SET WW_B.WWtype_ID = 1
FROM WW_B
INNER JOIN New_data
  on WW_B.StdNr = New_data.StdNr

Upvotes: -1

Related Questions