[AccessD] Operation must use an updatable query

William Benson (VBACreations.Com) vbacreations at gmail.com
Wed Jun 8 12:43:07 CDT 2011


Ahh--- well, Asger, thank you for your correction - that was merely a typo (corrected below). 

Is it so that there is no way to improve syntax of a group by query being used to update records in a table without dealing with primary keys in both sources?

I have tried this many ways, eventually settling on First() thinking this would eliminate redundancies, which I thought might have been the issue.

Actually, there were far MORE redundant records when I was joining table to table instead of table to group-by-query. 






....
I am getting a message that "operation must use an updatable query" from attempting to run this SQL. The reason I decided to try to join on a query is that TblSites has tremendous duplication in it, and I would update based on the TblSites table and it would be reported that many times more rows in Tbl_Matched_Sites were about to be updated than in fact there even were in the table. So I thought I might be able to improve performance by changing the join to a group by query. Is there a way to get this to work?

UPDATE Tbl_Matched_Sites INNER JOIN 

(Select 
          First(SITE_DB) as FirstOfSITE_DB,
          FirstOf(Site_Station_Name) as FirstOf Site_Station_Name,
          First(Address_Line1) as FirstOfAddress_Line1,
          First(City) as FirstOfCity,
          First(State) as FirstOfState

>From 
          TblSites

Group By
          Site_Station_Name,
          Address_Line1,
          city,
          State  )

as   MainInfo  ON


Tbl_Matched_Sites.GIB_SITE_DB = MainInfo.FirstOfSITE_DB 

SET 

Tbl_Matched_Sites.GIB_Phys_Addr_1 = MainInfo.[ FirstOfAddress_Line1], Tbl_Matched_Sites.GIB_Phys_City = MainInfo.[ FirstOfCity], Tbl_Matched_Sites.GIB_Phys_State = MainInfo.[ FirstOfState]

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list