T-SQL中IN子查询返回结果但UPDATE操作0行受影响问题

问题根源排查及解决方案 #

你当前语句存在3个核心错误，直接导致匹配不到更新行：

• 最核心的逻辑错误：你UPDATE的是MessageCenter_Receiver表的ID字段（接收关系表主键），但IN子查询返回的是MessageCenter.id（消息主表主键），两个完全不同字段的取值自然无法匹配，所以返回0行受影响。
• 表名引用错误：子查询WHERE条件里出现了未定义的表名EFP_MessageCenter_Receiver，你前面关联的表名是MessageCenter_Receiver，属于拼写笔误。
• 关联歧义问题：你在子查询中重复关联了MessageCenter_Receiver表，和外层UPDATE的表产生了不必要的逻辑歧义，更容易触发错误。

修正写法推荐 #

优先推荐使用UPDATE JOIN写法，逻辑更清晰，也能避免IN子查询的匹配问题：

UPDATE r
SET Displayed = 1
FROM MessageCenter_Receiver r
INNER JOIN MessageCenter m ON r.MessageID = m.id
WHERE r.UserID = 65
AND m.MessageSender = 'mmm'
AND (r.Displayed IS NULL OR r.Displayed = 0)

如果要保留IN子查询的写法，需要把返回字段修改为接收关系表的ID：

UPDATE MessageCenter_Receiver SET Displayed = 1 
WHERE ID IN 
(SELECT r.id FROM MessageCenter m
INNER JOIN MessageCenter_Receiver r ON m.id = r.MessageID 
WHERE r.UserID = 65
AND m.MessageSender = 'mmm'
AND (r.Displayed IS NULL OR r.Displayed = 0))

验证建议 #

执行更新前可以把UPDATE语句替换为SELECT * FROM 对应关联表和条件，先确认匹配行数是否为你要更新的5条，再执行更新操作避免误改数据。

内容的提问来源于stack exchange，提问作者Stig Kølbæk