SCD Type2员工表误更新后的SQL恢复方案问询

问题背景 #

我有一张Employee类型2缓慢变化维度（SCD Type2）表，初始正确数据如下：

Id Name   from_date    to_date     current_ind
1 Rohit   2011-01-06   2019-02-01    N
1 Rohit   2019-02-02   9999-12-31    Y
2 Virat   2008-01-01   9999-12-31    Y
3 Gill    2020-01-15   9999-12-31    Y
4 Mahi    2004-05-07   2011-02-01    N
4 Mahi    2011-02-02   9999-12-31    Y
5 Ajay    2022-01-15   9999-12-31    Y

但有人对该表进行了误操作更新，当前表数据已提交且无审计表，错误数据如下：

Id Name   from_date    to_date     current_ind
1 Rohit   2011-01-06   2019-02-01    N
1 Rohit   2019-02-02   2023-07-18    N
2 Virat   2008-01-01   2023-07-18    N
3 Gill    2020-01-15   2023-07-18    N
4 Mahi    2004-05-07   2011-02-01    N
4 Mahi    2011-02-02   2023-07-18    N
5 Ajay    2022-01-15   2023-07-18    N

需要编写SQL UPDATE语句修正表数据，恢复至初始正确状态。

解决方案 #

通用SQL（适用于PostgreSQL、SQL Server等支持CTE的数据库） #

WITH latest_employee AS (
    SELECT Id, MAX(from_date) AS max_from_date
    FROM Employee
    GROUP BY Id
)
UPDATE e
SET to_date = '9999-12-31',
    current_ind = 'Y'
FROM Employee e
JOIN latest_employee le ON e.Id = le.Id AND e.from_date = le.max_from_date
WHERE e.to_date = '2023-07-18' AND e.current_ind = 'N';

MySQL 适配版本 #

UPDATE Employee e
JOIN (
    SELECT Id, MAX(from_date) AS max_from_date
    FROM Employee
    GROUP BY Id
) le ON e.Id = le.Id AND e.from_date = le.max_from_date
SET e.to_date = '9999-12-31',
    e.current_ind = 'Y'
WHERE e.to_date = '2023-07-18' AND e.current_ind = 'N';

说明 #

• 该语句通过子查询/CTE定位每个员工ID对应的最新记录（即from_date最大的条目，也就是SCD Type2中原本的当前生效记录）。
• 仅修改那些被误操作的记录：to_date被改为2023-07-18且current_ind被改为N的条目，避免影响原本状态正确的历史记录（如ID=1的第一条、ID=4的第一条）。
• 将目标记录的to_date恢复为SCD Type2标准的永久生效值9999-12-31，current_ind恢复为Y标识当前生效。

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