使用带MAX聚合函数的内连接更新表时遇‘Operation must use an updateable query’错误

问题场景 #

需要用Table2中对应item的最大值更新Table1的value字段，表结构如下：

Table1 #

Table2 #

使用以下SQL语句执行更新时，抛出错误：

DoCmd.RunSQL "UPDATE table1 AS t1 INNER JOIN  " & _
"(SELECT table1.item, MAX(table2.value) AS maxvalue FROM table1 INNER JOIN table2  " & _ 
"ON table1.item = table2.item GROUP BY table1.item) AS t2  " & _
"ON t1.item = t2.item SET t1.value = t2.maxvalue " 

错误提示：

Operation must use an updateable query

移除MAX函数后语句可正常执行，但业务需求必须用最大值更新。

错误原因 #

原查询中使用了包含GROUP BY和MAX的聚合子查询作为关联表，在Access中，聚合查询属于不可更新查询，将其作为更新查询的数据源时，整个更新查询会被判定为不可更新，从而触发该错误。

解决方案 #

方法一：使用关联子查询直接获取最大值 #

直接在SET子句中通过子查询获取对应item的最大值，无需关联聚合表，Access可识别为可更新查询：

DoCmd.RunSQL "UPDATE table1 AS t1 " & _
"SET t1.value = (SELECT MAX(table2.value) FROM table2 WHERE table2.item = t1.item) " & _
"WHERE EXISTS (SELECT 1 FROM table2 WHERE table2.item = t1.item)"

• 加WHERE EXISTS是为了避免Table2中无对应item时，Table1的value被设为Null，若不需要该过滤逻辑，可删除WHERE子句。

方法二：通过临时表中转聚合结果 #

若数据量较大，子查询性能不佳，可先将Table2的聚合结果存入临时表，再关联更新：

-- 1. 创建临时表存储各item的最大值
DoCmd.RunSQL "SELECT item, MAX(value) AS maxvalue INTO temp_max_values FROM table2 GROUP BY item"
-- 2. 用临时表更新Table1
DoCmd.RunSQL "UPDATE table1 AS t1 INNER JOIN temp_max_values AS t2 ON t1.item = t2.item SET t1.value = t2.maxvalue"
-- 3. 清理临时表（可选）
DoCmd.RunSQL "DROP TABLE temp_max_values"

内容的提问来源于stack exchange，提问作者MBMSOFT