"Operation must use an updateable query" 1

[mkallover]

I don't understand why I am receiving this error when I try to run the following query:

UPDATE tblSancPres
SET IngID = (SELECT ING_PRESBR_ID FROM ING_PRESBR_NPI WHERE ING_PRESBR_NPI.PRESBR_NPI = tblSancPres.PrescID)
WHERE EXISTS
(SELECT ING_PRESBR_ID FROM ING_PRESBR_NPI WHERE ING_PRESBR_NPI.PRESBR_NPI = tblSancPres.PrescID);

When I try to run this query I get the error "Operation must use an updateable query." tblSancPres is a table that I created that is updateable. The ING_PRESBR_NPI is a network table for which I have read-only privileges but that shouldn't matter should it?

 

[Golom]

You need something like

UPDATE tblSancPres As S INNER JOIN ING_PRESBR_ID As P
       ON S.PrescID = P.PRESBR_NPI
 
SET S.IngID = P.ING_PRESBR_ID

The EXISTS clause is redundant since the ON condition of the join guarantees that such records do exist.

 

[mkallover]

Thanks. Strange that the first query seems to be the most popular result when I Googled how to get this done. The second query is much more simple!

 

[Golom]

I suspect that you got the "must use updatable" message because the ING_PRESBR_NPI table, being read-only, is not updatable.

True ... you are not trying to update that table but, because of the rather odd construction of the SQL, the parser may have become confused and decided that the read-only status prevented an update.

That syntax may work in Oracle, SQL Server or other DBMS systems but it obviously has problems in Access.