使用CTE更新people表is_student字段结果异常，如何修复？

问题原因 #

你当前的UPDATE语句会让people表的每一行与student_population的所有行进行笛卡尔关联，每一条person_id会被多次更新，最终保留的是最后一次匹配的结果。比如person_id=004会依次和student_id=003（设为'F'）、student_id=004（设为'T'）、student_id=005（设为'F'）匹配，最终被覆盖为'F'，这就是只有第一个学生ID显示'T'的原因。

修复方法 #

方法1：使用EXISTS子句判断

通过EXISTS检查当前人员ID是否存在于学生集合中，避免多次更新：

WITH student_population AS (select ... from...)
UPDATE people p
SET is_student = CASE 
    WHEN EXISTS (
        SELECT 1 FROM student_population stu
        WHERE stu.student_id = p.person_id
    ) THEN 'T' 
    ELSE 'F' 
END;

方法2：使用LEFT JOIN关联

通过LEFT JOIN关联两张表，根据是否匹配到学生ID来设置值：

WITH student_population AS (select ... from...)
UPDATE people p
LEFT JOIN student_population stu ON p.person_id = stu.student_id
SET is_student = CASE 
    WHEN stu.student_id IS NOT NULL THEN 'T' 
    ELSE 'F' 
END;

方法3：使用IN子句判断

直接用IN子句检查人员ID是否在学生ID集合中：

WITH student_population AS (select ... from...)
UPDATE people p
SET is_student = CASE 
    WHEN p.person_id IN (SELECT student_id FROM student_population) THEN 'T' 
    ELSE 'F' 
END;

以上三种方法都能正确将is_student字段更新为预期的'T'或'F'，可根据你的SQL方言偏好选择使用。

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