Access update query syntax error (missing operator)

Question

This appears to be a fairly common problem in Access, but after researching multiple threads and trying all types of variations, I still can't find a solution for my problem.

Here is a simple Select query that runs just fine which pulls data from an Access table and a linked Oracle table:

SELECT a.WELL_UWI, b.MAIN_FORM
FROM  (SELECT WELL_UWI, MAIN_FORM
      FROM tmp_form) AS b
INNER JOIN eauser_nfx_hz_well_summary AS a
ON b.WELL_UWI = a.WELL_UWI;

I modified it to be an update query to update the linked Oracle table from data in the Access table as follows:

UPDATE a
SET a.MAIN_HZ_FM = b.MAIN_FORM
FROM  (SELECT WELL_UWI, MAIN_FORM
      FROM tmp_form) AS b
INNER JOIN eauser_nfx_hz_well_summary AS a
ON b.WELL_UWI = a.WELL_UWI;

I end up with this message:

Syntax error (missing operator) in query expression 'b.MAIN_FORM FROM (SELECT WELL_UWI, MAIN_FORM FROM tmp_form) AS b INNER JOIN eauser_nfx_hz_well_summary AS a ON b.WELL_UWI = a.WELL_UWI

Any idea what I'm missing?

Thanks!

All, just an FYI, I did find an answer to this problem. It had to do with a bug in the Oracle driver I was using. The answer is detailed in my other post found here: stackoverflow.com/questions/49515633/… — Heather
– Heather, Commented Apr 2, 2018 at 13:02

Erik A · Accepted Answer · 2018-03-28 20:19:31Z

3

Access has a different syntax for updates. All tables are specified directly after the UPDATE keyword, and there's no FROM:

UPDATE (SELECT WELL_UWI, MAIN_FORM
      FROM tmp_form) AS b
INNER JOIN eauser_nfx_hz_well_summary AS a
ON b.WELL_UWI = a.WELL_UWI
SET a.MAIN_HZ_FM = b.MAIN_FORM;

Note that in Access, the full query (including all subqueries) needs to be updateable. You can't specify you're only updating a specific table.

When you have a problem with locks or non-updateable tables, you can often use a DLookUp to avoid these problems:

UPDATE eauser_nfx_hz_well_summary AS a
SET a.MAIN_HZ_FM = DLookUp("MAIN_FORM", "tmp_form", "WELL_UWI = '" & a.WELL_UWI & "'")

edited Mar 28, 2018 at 20:19

answered Mar 28, 2018 at 16:30

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Heather Over a year ago

I ran the query as you have it above, but ended up with lock violations, which is a problem that I've been having from the very beginning of this issue. If I were to use the query above to update one record, it would update with no issues; but it cannot update more than one record at a time. I believe this is due to some problem with the INNER JOIN, or it is trying to update both tables as opposed to just the eauser_nfx_hz_well_summary table.

Erik A Over a year ago

@Heather See the edit. If WELL_UWI is numerical, you should remove the single quotes. I'm assuming it's a string.

Heather Over a year ago

Oh, I was so hoping your solution would work because this has been causing me nothing but headaches all day, but I'm still getting the lock violation error message. :(

Erik A Over a year ago

Is tmp_form a query? This last way should only set a lock on the table being updated, unless you have different objects/processes locking things.

Heather Over a year ago

No, it is a table. I think a lot of the problem is the table I am updating is a linked Oracle table, but the table I am using to update the Oracle table is an Access table.

|

Salim · Accepted Answer · 2018-04-01 22:05:48Z

0

I don't have access to test this SQL out, but I think it will work. You were using wrong syntax, UPDATE statement can not be followed by FROM clause.

UPDATE eauser_nfx_hz_well_summary a
SET a.MAIN_HZ_FM = 
(SELECT b.MAIN_FORM from tmp_form b INNER JOIN eauser_nfx_hz_well_summary a1 ON b.WELL_UWI = a1.WELL_UWI AND a.WELL_UWI = a1.WELL_UWI)

edited Apr 1, 2018 at 22:05

answered Mar 28, 2018 at 16:38

Salim

2,18814 silver badges15 bronze badges

2 Comments

Heather Over a year ago

I ran the query as you have it above, but got the same syntax error message on the whole Select statement. I then tried removing the last part (the AND statement), but got the "Operation must use an updateable query" error. These are the two error messages I keep running into over and over.

Salim Over a year ago

Sorry to hear that. I don't have access so I tried it in Oracle because ANSI standard should be uniformly adopted by both. The following sql got compiled. UPDATE eauser_nfx_hz_well_summary a SET a.MAIN_HZ_FM = (SELECT b.MAIN_FORM from tmp_form b INNER JOIN eauser_nfx_hz_well_summary a1 ON b.WELL_UWI = a1.WELL_UWI AND a.WELL_UWI = a1.WELL_UWI) ;

Collectives™ on Stack Overflow

Access update query syntax error (missing operator)

2 Answers 2

9 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related