MS Access中UPDATE与SELECT语句返回行数不一致问题求助

我的问题和Stack Overflow上某帖完全一致，但我用的是MS Access，原帖解决方案不适用（或我操作有误）。

以下两条语句返回的行数不同：

SELECT c.hole_id, c.level, c.section, c.valid, c.holetype, a.hole_id
FROM collar AS c LEFT JOIN assay AS a
ON a.hole_id = c.hole_id
WHERE c.holetype="Channel" AND a.hole_id Is Null;

以及

UPDATE collar AS c 
SET c.valid = no
WHERE EXISTS
     (SELECT c.hole_id, c.level, c.section, c.valid, c.holetype, a.hole_id   
     FROM collar AS c LEFT JOIN assay AS a
     ON a.hole_id = c.hole_id
     WHERE c.holetype="Channel" AND a.hole_id Is Null);

核心需求：将collar表中所有在assay表无关联记录（hole_id为主键/外键）的Channel类型记录，其valid字段设为no。

我知道UPDATE存在自连接问题，尝试了下面的语句但没有任何行被更新：

UPDATE collar AS c, assay AS a
SET c.valid = no
WHERE a.hole_id = c.hole_id AND c.holetype="Channel" AND a.hole_id Is Null;

求解决建议？

解决方案 #

针对MS Access的语法特性，你可以用以下两种方式解决：

方法一：使用NOT EXISTS子查询

MS Access对NOT EXISTS的支持更稳定，直接检查当前collar记录的hole_id不存在于assay表中：

UPDATE collar AS c
SET c.valid = 'no'
WHERE c.holetype = 'Channel'
AND NOT EXISTS (
    SELECT 1
    FROM assay AS a
    WHERE a.hole_id = c.hole_id
);

子查询仅需返回存在性判断，用SELECT 1比选取全字段更高效；同时注意字符串值no要加单引号（MS Access中字符串常量推荐用单引号，避免与双引号的标识符冲突）。

方法二：使用左连接的UPDATE语法

MS Access支持通过左连接直接更新，逻辑和你最初的SELECT语句完全对应：

UPDATE collar AS c
LEFT JOIN assay AS a ON c.hole_id = a.hole_id
SET c.valid = 'no'
WHERE c.holetype = 'Channel'
AND a.hole_id IS NULL;

这种写法直接以左连接结果为更新依据，避免了子查询的作用域冲突问题。

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