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: https://stackoverflow.com/questions/49515633/access-update-query-of-linked-oracle-tables-not-updating-properly?noredirect=1#comment86036833_49515633 — Heather, Apr 02 '18 at 13:02

Erik A · Accepted Answer · 2018-03-28T20:19:31.413

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 '18 at 20:19

answered Mar 28 '18 at 16:30

Erik A

31,639
12
42
67

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. – Heather Mar 28 '18 at 18:22
@Heather See the edit. If `WELL_UWI` is numerical, you should remove the single quotes. I'm assuming it's a string. – Erik A Mar 28 '18 at 20:20
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. :( – Heather Mar 28 '18 at 20:37
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. – Erik A Mar 28 '18 at 20:39
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. – Heather Mar 28 '18 at 20:40
Oh.... That's info that should be in the question. Oracle is notorious for weird locking problems. You could upload your data to a temporary table in Oracle, and execute the update using a passthrough query. I don't have that much experience with Oracle, so there might be alternatives. – Erik A Mar 28 '18 at 20:43
Darn it, I thought I had put that in my explanation. Sorry about that. I'll add it now. – Heather Mar 28 '18 at 20:45
Please post the locking error message. In fact, you may need to ask a different question and close out this one as the `UPDATE` should be resolved. – Parfait Mar 28 '18 at 21:02
I will go ahead and close this question, but I did post a question on the lock error previously. It can be found here: https://stackoverflow.com/questions/49515633/access-update-query-of-linked-oracle-tables-not-updating-properly?noredirect=1#comment86036833_49515633 – Heather Mar 28 '18 at 21:34

Salim · Answer 2 · 2018-04-01T22:05:48.840

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 01 '18 at 22:05

answered Mar 28 '18 at 16:38

Salim

2,046
12
13

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. – Heather Mar 28 '18 at 18:13
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) ; – Salim Apr 01 '18 at 22:04

Access update query syntax error (missing operator)

2 Answers2