"Operation must use an updatable query" - driving me nuts

[JonR]

UPDATE [AP Reporting Table] LEFT JOIN REAINP01_EP_CONFIG_SN ON [AP Reporting
Table].ENDPT_LABEL = REAINP01_EP_CONFIG_SN.ENDPT_LABEL SET [AP Reporting
Table].[Migrated?] = "Removed"
WHERE (((REAINP01_EP_CONFIG_SN.ENDPT_LABEL) Is Null))
WITH OWNERACCESS OPTION;


AP Reporting Table is local. REAIN01_EP_CONFIG_SN is ODBC linked into the
Access database. The idea here is that the REAIN table is current but does
not retain any history, where the AP table keeps history. If an endpoint is
no longer found on REAIN, it is considered "Removed" and the [Migrated?]
field in the AP table should reflect this. But the query doesn't work. I
can find removed endpoints with a select query, but can't get the table to
update.
--
HTH

JonR

Please rate your posts

 

[John W. Vinson]

UPDATE [AP Reporting Table] LEFT JOIN REAINP01_EP_CONFIG_SN ON [AP Reporting
Table].ENDPT_LABEL = REAINP01_EP_CONFIG_SN.ENDPT_LABEL SET [AP Reporting
Table].[Migrated?] = "Removed"
WHERE (((REAINP01_EP_CONFIG_SN.ENDPT_LABEL) Is Null))
WITH OWNERACCESS OPTION;


AP Reporting Table is local. REAIN01_EP_CONFIG_SN is ODBC linked into the
Access database. The idea here is that the REAIN table is current but does
not retain any history, where the AP table keeps history. If an endpoint is
no longer found on REAIN, it is considered "Removed" and the [Migrated?]
field in the AP table should reflect this. But the query doesn't work. I
can find removed endpoints with a select query, but can't get the table to
update.

Access cannot enforce referential integrity across different backends. Rather
than a LEFT JOIN consider using a NOT EXISTS clause:

UPDATE [AP Reporting Table]
SET [Migrated?] = "Removed"
WHERE NOT EXISTS
(SELECT ENDPT_LABEL FROM REAINP01_EP_CONFIG_SN
WHERE REAINP01_EP_CONFIG_SN.ENDPT_LABEL = [AP Reporting Table].[ENDPT_LABEL])

This would be slower than a left-join if you could do a left-join... but it
should resolve the updatability issue.

 

[JonR]

Thanks John,

That seemed to do the trick, but you weren't kidding about it being slow.
The query is taking about two hours to run. Any suggestions on how to speed
things up?

Thnks

JonR

Please rate your posts

UPDATE [AP Reporting Table] LEFT JOIN REAINP01_EP_CONFIG_SN ON [AP Reporting
Table].ENDPT_LABEL = REAINP01_EP_CONFIG_SN.ENDPT_LABEL SET [AP Reporting
Table].[Migrated?] = "Removed"
WHERE (((REAINP01_EP_CONFIG_SN.ENDPT_LABEL) Is Null))
WITH OWNERACCESS OPTION;


AP Reporting Table is local. REAIN01_EP_CONFIG_SN is ODBC linked into the
Access database. The idea here is that the REAIN table is current but does
not retain any history, where the AP table keeps history. If an endpoint is
no longer found on REAIN, it is considered "Removed" and the [Migrated?]
field in the AP table should reflect this. But the query doesn't work. I
can find removed endpoints with a select query, but can't get the table to
update.

Access cannot enforce referential integrity across different backends. Rather
than a LEFT JOIN consider using a NOT EXISTS clause:

UPDATE [AP Reporting Table]
SET [Migrated?] = "Removed"
WHERE NOT EXISTS
(SELECT ENDPT_LABEL FROM REAINP01_EP_CONFIG_SN
WHERE REAINP01_EP_CONFIG_SN.ENDPT_LABEL = [AP Reporting Table].[ENDPT_LABEL])

This would be slower than a left-join if you could do a left-join... but it
should resolve the updatability issue.

 

[John W. Vinson]

Thanks John,

That seemed to do the trick, but you weren't kidding about it being slow.
The query is taking about two hours to run. Any suggestions on how to speed
things up?

Indexes on the join fields of course... but it's got to run the subquery
repeatedly as many times as there are records in the main query. Unless you
can (temporarily maybe?) import one or the other table so they're in the same
database, I don't know what can be done.

 

[JonR]

You've confirmed my suspicions. Thanks again


JonR

Please rate your posts

 

[John Spencer MVP]

Does the following query work and return the ENDPT_Label for the records you
want to update?

SELECT [AP Reporting Table].ENDPT_LABEL
FROM [AP Reporting Table] LEFT JOIN REAINP01_EP_CONFIG_SN
ON [AP Reporting Table].ENDPT_LABEL = REAINP01_EP_CONFIG_SN.ENDPT_LABEL
WHERE REAINP01_EP_CONFIG_SN.ENDPT_LABEL Is Null

If so, try the following.
UPDATE [AP Reporting Table]
SET [AP Reporting Table].[Migrated?] = "Removed"
WHERE [AP Reporting Table].ENDPT_LABEL IN
(SELECT [AP Reporting Table].ENDPT_LABEL
FROM [AP Reporting Table] LEFT JOIN REAINP01_EP_CONFIG_SN
ON [AP Reporting Table].ENDPT_LABEL = REAINP01_EP_CONFIG_SN.ENDPT_LABEL
WHERE REAINP01_EP_CONFIG_SN.ENDPT_LABEL Is Null)

It is similar to John Vinson's query but avoids using a correlated subquery.
The sub-query in the above where clause runs ONE TIME. The exists query has
to run once for every record in [AP Reporting Table].

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County