When performing update queries against a Microsoft JET database, you may come across the following error:

Operation must use an updateable query

The Microsoft Knowledge Base has an article for the most obvious cause of this error, that the database file is not writable. However, you may also receive this error when trying to run an UPDATE query which contains a subquery:

UPDATE [Orders]
   SET [Status ID] = ( SELECT TOP 1 [Status ID]
                         FROM [Orders Status]
                        ORDER BY [Status ID] );

I encountered this error when trying to write an update query that was valid JETSQL and TSQL. Unfortunately it’s not possible to write the query in a way that is valid SQL on both platforms¹ – to work around this error in JET you need to change the way the query is structured:

UPDATE [Orders]
       , ( SELECT TOP 1 [Status ID]
             FROM [Orders Status]
            ORDER BY [Status ID] ) AS [tmpSubquery]
   SET [Orders].[Status ID] = [tmpSubquery].[Status ID];

The query now runs, but we’re not out of the woods yet. Let’s try writing a similar query that would achieve the same thing:

UPDATE [Orders]
       , ( SELECT MIN([Status ID]) AS [Min Status ID]
             FROM [Orders Status] ) AS [tmpSubquery]
   SET [Orders].[Status ID] = [tmpSubquery].[Min Status ID];

This query does not run – it fails with the same error. The problem is our use of an aggregate function (in this case, MIN).

To work around this we must take a departure from ANSI SQL and use the Domain Aggregate functions in Access.

UPDATE [Orders]
   SET [Status ID] = DMin("[Status ID]", "[Orders Status]")

This works, and can even be extended to emulate a correlated subquery via each domain aggregate function’s optional criteria argument.

¹ JETSQL and TSQL have differing syntax for update queries. TSQL requires you to use the FROM clause when the update involves multiple tables, JETSQL does not (in line with ANSI SQL). Thus to write an update query that works on multiple platforms you need to use a subquery.

Update

Turns out there is a Knowledge Base article for this problem, for Access 2.0, and describes 3 workarounds. The post above describes the third workaround. Note that the first workaround may not work if you’re inside a transaction (it uses DDL), so I recommend looking at workarounds two and three.