SQL游标搭配WHERE不等条件失效问题求助

1. 先修复语法错误 #

原变量声明段里，@VAR VARCHAR(MAX)后缺少逗号，这会直接导致语法报错，是游标无法正常运行的直接原因之一：

DECLARE 
@NUMBER INT,
@MATRICOLA INT,
@VAR VARCHAR(MAX), -- 补充缺失的逗号
@ID INT;

2. 解决WHERE条件的NULL匹配问题 #

TABLE1.[VAR] <> TABLE2.[VAR]无法处理任意一方为NULL的场景——SQL中NULL与任何值的比较结果都是UNKNOWN，不会被WHERE条件筛选，这会导致部分需要更新的记录被遗漏。可以用两种方式修复：

兼容多数数据库的通用写法 #

手动覆盖NULL的情况：

WHERE (TABLE1.[VAR] <> TABLE2.[VAR] 
       OR TABLE1.[VAR] IS NULL AND TABLE2.[VAR] IS NOT NULL 
       OR TABLE1.[VAR] IS NOT NULL AND TABLE2.[VAR] IS NULL)

针对支持IS NOT DISTINCT FROM的数据库（如SQL Server 2022+、PostgreSQL） #

可以简化为更简洁的写法：

WHERE TABLE1.[VAR] IS NOT DISTINCT FROM TABLE2.[VAR]

3. 更高效的替代方案：放弃游标，直接用UPDATE JOIN #

游标处理批量更新效率极低，推荐直接用UPDATE JOIN一次性完成更新，逻辑更简洁且性能更优：

UPDATE t1
SET t1.[VAR] = t2.[VAR]
FROM TABLE1 t1
JOIN TABLE2 t2 
  ON t1.NUMERO = t2.NUMERO 
  AND t1.MATRICOLA = t2.MATRICOLA
WHERE (t1.[VAR] <> t2.[VAR] 
       OR t1.[VAR] IS NULL AND t2.[VAR] IS NOT NULL 
       OR t1.[VAR] IS NOT NULL AND t2.[VAR] IS NULL)

注：若不需要将TABLE1中无TABLE2匹配的记录VAR设为NULL，可保留LEFT JOIN并在WHERE中添加t2.[VAR] IS NOT NULL

验证逻辑 #

先执行以下查询确认待更新记录是否正确：

SELECT t1.ID, t1.[VAR] AS OLD_VAR, t2.[VAR] AS NEW_VAR
FROM TABLE1 t1
JOIN TABLE2 t2 
  ON t1.NUMERO = t2.NUMERO 
  AND t1.MATRICOLA = t2.MATRICOLA
WHERE (t1.[VAR] <> t2.[VAR] 
       OR t1.[VAR] IS NULL AND t2.[VAR] IS NOT NULL 
       OR t1.[VAR] IS NOT NULL AND t2.[VAR] IS NULL)

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