update query error help

[raylward102]

I have an update query that keeps erroring out; can anyone spot the syntax error; I can't!

UPDATE b SET b.[type] = a.[type], b.variety = a.variety FROM [lotsDB] AS a INNER JOIN [processed] AS b ON (b.[lotID] = a.[ID])


The vague error:
Syntax error (missing operator) in query expression 'a.variety FROM [lotsDB] AS a INNER JOIN [processed] AS b ON (b.[lotID] = a.[ID])'.



I can tell you this; everything in the processed table has a parent in the lotsdb table. the join on lotID to ID are same value types.

I should add, that I'm running this query against a access.mdb databse via adodb in excel. I'm usually pretty good at this stuff and right queries all the time, but this one has stumped me.

[Micron]

Removed answer. In spite of looking closely, I missed something.
There's something to be said for posting code/sql within code tags. Makes things easier to read, harder to miss and doesn't interject spaces where they don't belong.

BTW, why alias these tables when they're not the same table and the names are not overly long?
Maybe try this (aliasing) as a Select query first. I don't see the issue - sorry.

[raylward102]

that's a pasted query; Not sure I'm seeing a lone parenthesis you speak of; I see 2 surrounding the join values (could be part of the problem; the fact that I'm not seeing what your seeing??)

UPDATE b SET b.[type] = a.[type], b.variety = a.variety FROM [lotsDB] AS a INNER JOIN [processed] AS b ON (b.[lotID] = a.[ID])

[Micron]

I guess you replied while I was editing. Review revised post.

[June7]

Did you use Access query builder to get SQL? I did and shows the following.

JOIN goes before SET, no FROM clause.

UPDATE lotsDB AS a INNER JOIN [processed] AS b ON a.ID = b.[lotID] SET b.[type] = a.[type], b.variety = a.variety

[raylward102]

I didn't use any builder but primarily write update queries for SQL; if what your saying is correct, I've probably written this inappropriately for access; I will quickly adjust according to your logic and see if that helps.

[raylward102]

Thanks Micro and JUNE7!, for spotting my error. Was driving me nuts!