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

Question

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?

dcbeckman · Accepted Answer · 2018-07-03 21:30:43Z

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.

Danny Beckett · Accepted Answer · 2013-07-01 09:52:13Z

-1

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

answered Jul 1, 2013 at 9:52

Danny Beckett

21k27 gold badges114 silver badges144 bronze badges

Comments

mohkhan · Accepted Answer · 2013-07-01 09:52:37Z

-1

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

answered Jul 1, 2013 at 9:52

mohkhan

12.3k2 gold badges27 silver badges27 bronze badges

1 Comment

Sceptical Jule Over a year ago

Thanks for your answers but I still get the same error "...in expression '1 FROM WW_B INNER JOIN New_data on WW_B.StdNr = New_data.StdNr'." Besides, why do I have to declare the table name using FROM if I already did that with UPDATE?

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related