MS Access中含子查询的UPDATE语句报错问题排查

问题描述 #

在MS Access中执行带聚合子查询的UPDATE语句时，出现两种不同错误：

第一种写法及报错 #

执行语句：

UPDATE SIRADATOK T1 INNER JOIN (SELECT sirid, MAX(SzolgaltatasokDatumig) AS MAXDATE
FROM ADOK GROUP BY SIRID HAVING MAX(SzolgaltatasokDatumig)<>' ' AND
MAX(SzolgaltatasokDatumig) IS NOT NULL)  AS T2 ON T1.SIRID=T2.sirid 
SET MegvaltasIdeje = MAXDATE;

报错信息：

"Operation must use updatable query"（操作必须使用可更新查询）

第二种写法及报错 #

执行语句：

UPDATE T2 
SET T1.MegvaltasIdeje = T2.MAXDATE
FROM SIRADATOK T1, (SELECT sirid, MAX(SzolgaltatasokDatumig) AS MAXDATE 
FROM ADOK GROUP BY SIRID HAVING MAX(SzolgaltatasokDatumig)<>' ' 
AND MAX(SzolgaltatasokDatumig) IS NOT NULL)  T2 
WHERE  T1.SIRID=T2.sirid

报错信息：

"Syntax error (missing operator) in query expression 'T2.MAXDATE FROM SIRADATOK T1'."（查询表达式'T2.MAXDATE FROM SIRADATOK T1'中存在语法错误（缺少运算符））

解决方法 #

MS Access的UPDATE语法有专属规则，针对上述问题可采用以下两种可行方案：

方案1：使用域聚合函数替代JOIN子查询 #

利用DMax函数直接获取对应SIRID的有效最大日期，避免聚合子查询导致的不可更新问题：

UPDATE SIRADATOK
SET MegvaltasIdeje = DMax("SzolgaltatasokDatumig", "ADOK", "SIRID = '" & SIRID & "' AND SzolgaltatasokDatumig <> ' ' AND SzolgaltatasokDatumig IS NOT NULL")
WHERE EXISTS (
    SELECT 1 FROM ADOK 
    WHERE ADOK.SIRID = SIRADATOK.SIRID 
    AND SzolgaltatasokDatumig <> ' ' 
    AND SzolgaltatasokDatumig IS NOT NULL
);

• DMax函数：从ADOK表中筛选当前SIRID对应的有效最大日期
• 外层WHERE EXISTS确保只更新有匹配有效数据的记录，避免无数据时设置为NULL

方案2：使用相关子查询的UPDATE写法 #

通过相关子查询直接获取对应值，同时用EXISTS过滤无效记录，符合Access的语法规则：

UPDATE SIRADATOK
SET MegvaltasIdeje = (
    SELECT MAX(SzolgaltatasokDatumig)
    FROM ADOK
    WHERE ADOK.SIRID = SIRADATOK.SIRID
    AND SzolgaltatasokDatumig <> ' '
    AND SzolgaltatasokDatumig IS NOT NULL
)
WHERE EXISTS (
    SELECT 1 FROM ADOK 
    WHERE ADOK.SIRID = SIRADATOK.SIRID 
    AND SzolgaltatasokDatumig <> ' ' 
    AND SzolgaltatasokDatumig IS NOT NULL
);

报错原因说明 #

• 第一种写法报错：Access中，包含聚合函数的子查询作为JOIN对象时，查询会变成不可更新的快照，无法执行UPDATE操作
• 第二种写法报错：Access不支持SQL Server风格的UPDATE ... FROM ...语法，必须使用其专属的UPDATE格式

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