如何基于条件更新指定行？Clearing标识列赋值错误修复

需求回顾 #

需要为REP_ConfirmationAndClearing_Final表添加两个标识列：

• In Clearing?：标记申请人是否经历Clearing流程，仅当Sequence表中Decision Desc='UA Clearing'且Final表中Choice Number=7时，该行赋值为'Yes'
• Prior standard application?：标记该申请人在Clearing前是否有过标准申请，仅当申请人同时存在Choice Number=7和Choice Number=1-5的行时，7号选择行赋值为'Yes'
  仅允许更新Choice Number=7的行

错误原因 #

原脚本的核心问题在于：

1. 更新Final表时，仅通过UCAS Id关联，未限制Choice Number=7，导致同UCAS Id下的所有行（包括1-5）都被同步了标识值
2. 更新中间表SA_ConfirmationAndClearing_CurrentYearDecisionSequence_Clearing时，未关联Final表的Choice Number，导致该表中同UCAS Id的所有行都被设置了标识值

修正后的完整SQL脚本 #

DROP TABLE IF EXISTS SA_ConfirmationAndClearing_CurrentYearDecisionSequence_Clearing;

-- 复制源表数据到中间表
SELECT *
INTO SA_ConfirmationAndClearing_CurrentYearDecisionSequence_Clearing
FROM SA_ConfirmationAndClearing_CurrentYearDecisionSequence

-- 添加标识列
ALTER TABLE SA_ConfirmationAndClearing_CurrentYearDecisionSequence_Clearing 
ADD [In Clearing?] varchar(255),
    [Prior standard application?] varchar(255)

-- 创建临时表：按UCAS Id分类申请人的选择类型
DROP TABLE IF EXISTS #UCAS_ID_to_Choice_Number;

SELECT 
    Final.[UCAS Id],
    CASE
        -- 同时存在7号和1-5号选择的申请人
        WHEN EXISTS(SELECT 1 FROM [REP_ConfirmationAndClearing_Final] f1 WHERE f1.[UCAS Id] = Final.[UCAS Id] AND f1.[Choice Number] = '7')
             AND EXISTS(SELECT 1 FROM [REP_ConfirmationAndClearing_Final] f2 WHERE f2.[UCAS Id] = Final.[UCAS Id] AND f2.[Choice Number] IN ('1','2','3','4','5')) THEN '1-5 and 7 - Both'
        -- 仅存在7号选择的申请人
        WHEN EXISTS(SELECT 1 FROM [REP_ConfirmationAndClearing_Final] f3 WHERE f3.[UCAS Id] = Final.[UCAS Id] AND f3.[Choice Number] = '7') THEN 'Only 7 - Clearing'
        ELSE '1-5 - Nulls'
    END as [Choice Number Type]
INTO #UCAS_ID_to_Choice_Number
FROM [REP_ConfirmationAndClearing_Final] as Final
JOIN SA_ConfirmationAndClearing_CurrentYearDecisionSequence_Clearing as Sequence
    ON Final.[UCAS Id] = Sequence.[UCAS Id]
WHERE Sequence.[Decision Desc] = 'UA Clearing'
GROUP BY Final.[UCAS Id];

-- 更新中间表：仅更新关联到Final表中Choice Number=7的行
UPDATE Sequence
SET
    [In Clearing?] = CASE
                        WHEN UCASTempTable.[Choice Number Type] IN ('Only 7 - Clearing', '1-5 and 7 - Both') THEN 'Yes'
                        ELSE NULL
                     END,
    [Prior standard application?] = CASE
                                        WHEN UCASTempTable.[Choice Number Type] = '1-5 and 7 - Both' THEN 'Yes'
                                        ELSE NULL
                                     END
FROM SA_ConfirmationAndClearing_CurrentYearDecisionSequence_Clearing as Sequence
JOIN #UCAS_ID_to_Choice_Number as UCASTempTable
    ON Sequence.[UCAS Id] = UCASTempTable.[UCAS Id]
-- 关联Final表，仅处理对应Choice Number=7的记录
JOIN [REP_ConfirmationAndClearing_Final] as Final
    ON Sequence.[UCAS Id] = Final.[UCAS Id]
WHERE Sequence.[Decision Desc] = 'UA Clearing'
  AND Final.[Choice Number] = '7'; -- 关键限制：仅针对7号选择的行

-- 更新Final表：仅更新Choice Number=7的行
UPDATE Final
SET 
    Final.[In Clearing?] = COALESCE(Clearing.[In Clearing?], NULL),
    Final.[Prior standard application?] = COALESCE(Clearing.[Prior standard application?], NULL)
FROM [REP_ConfirmationAndClearing_Final] AS Final
INNER JOIN [SA_ConfirmationAndClearing_CurrentYearDecisionSequence_Clearing] AS Clearing
    ON Final.[UCAS Id] = Clearing.[UCAS Id]
WHERE Clearing.[Decision Desc] = 'UA Clearing'
  AND Final.[Choice Number] = '7'; -- 关键限制：仅更新7号选择的行

关键修正点 #

• 在更新中间表时，新增与Final表的关联，并添加Final.[Choice Number] = '7'的条件，确保仅为对应7号选择的行设置标识值
• 在更新Final表时，添加Final.[Choice Number] = '7'的条件，避免误更新1-5号选择的行
• 优化临时表的判断逻辑，使用EXISTS替代COUNT(DISTINCT)，逻辑更清晰且性能更优

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