CODEWITHSUNDEEP
sqloracle-databasems-access
Heather
Heather

Reputation: 989

Access update query syntax error (missing operator)

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!

Upvotes: 0

Views: 1488

Answers (2)

Salim
Salim

Reputation: 2178

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)

Upvotes: 0

Erik A
Erik A

Reputation: 32682

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 & "'")

Upvotes: 3

Related Questions