基于新DateEffective批量终止旧数据：表自关联更新需求

针对你遇到的PRID对应两条有效记录、需要将旧数据终止日期设为新数据生效日期前一天的需求，可以通过ROW_NUMBER()分区排序的方式实现，具体步骤如下：

1. 先验证目标数据（关键！避免误更新） #

先执行以下查询，确认每个PRID的新旧数据日期对应关系，确保计算出的新终止日期符合预期（比如PRID=1339的旧数据终止日期应显示为2021-02-18）：

WITH PRL_Ranked AS (
    SELECT 
        PRID,
        DateEffective,
        DateTerminated,
        -- 按PRID分区，生效日期倒序排序：rn=1是最新数据，rn=2是旧数据
        ROW_NUMBER() OVER (PARTITION BY PRID ORDER BY DateEffective DESC) AS rn
    FROM PRL
    WHERE GETDATE() BETWEEN DateEffective AND DateTerminated 
      AND SiteGID IN (190,191,192,193,30,31,32,33)
)
SELECT 
    r1.PRID,
    r1.DateEffective AS 旧数据生效日期,
    r1.DateTerminated AS 当前旧数据终止日期,
    r2.DateEffective AS 新数据生效日期,
    DATEADD(DAY, -1, r2.DateEffective) AS 待更新的旧数据终止日期
FROM PRL_Ranked r1
JOIN PRL_Ranked r2 ON r1.PRID = r2.PRID AND r1.rn = 2 AND r2.rn = 1
ORDER BY r1.PRID;

2. 执行更新操作 #

确认数据无误后，执行以下更新语句，将旧数据的DateTerminated更新为对应新数据DateEffective的前一天：

WITH PRL_Ranked AS (
    SELECT 
        PRID,
        DateEffective,
        DateTerminated,
        ROW_NUMBER() OVER (PARTITION BY PRID ORDER BY DateEffective DESC) AS rn
    FROM PRL
    WHERE GETDATE() BETWEEN DateEffective AND DateTerminated 
      AND SiteGID IN (190,191,192,193,30,31,32,33)
)
UPDATE r1
SET r1.DateTerminated = DATEADD(DAY, -1, r2.DateEffective)
FROM PRL_Ranked r1
JOIN PRL_Ranked r2 ON r1.PRID = r2.PRID AND r1.rn = 2 AND r2.rn = 1;

进阶过滤（确保只处理两条重复项的PRID） #

如果需要严格限定只处理你初始查询中筛选出的、PRID对应SiteID计数为2的记录，可以在逻辑中加入分组过滤：

WITH Target_PRIDs AS (
    SELECT PRID 
    FROM PRL
    WHERE GETDATE() BETWEEN DateEffective AND DateTerminated 
      AND SiteGID IN (190,191,192,193,30,31,32,33)
    GROUP BY PRID
    HAVING COUNT(SiteID) = 2
),
PRL_Ranked AS (
    SELECT 
        p.PRID,
        p.DateEffective,
        p.DateTerminated,
        ROW_NUMBER() OVER (PARTITION BY p.PRID ORDER BY p.DateEffective DESC) AS rn
    FROM PRL p
    JOIN Target_PRIDs tp ON p.PRID = tp.PRID
)
UPDATE r1
SET r1.DateTerminated = DATEADD(DAY, -1, r2.DateEffective)
FROM PRL_Ranked r1
JOIN PRL_Ranked r2 ON r1.PRID = r2.PRID AND r1.rn = 2 AND r2.rn = 1;

注意事项 #

• 务必先在测试环境验证并执行，确认结果符合预期后再操作生产数据
• 若后续出现PRID对应超过2条有效记录的情况，此逻辑仅会处理排序为第2的旧数据，需根据实际需求调整

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