Snowflake SCD Type 2 Merge语句报错，如何实现预期输出？

场景规则 #

• 当ID匹配且value1、value2均匹配时，不做操作
• 当ID匹配但value1或value2不匹配时，更新现有目标记录（设置end_date为当日、active_flag为N）并插入新记录（end_date为null、active_flag为Y）
• 当ID不匹配时，插入新记录（end_date为null、active_flag为Y）

原错误语句分析 #

原Merge语句报错'MATCHED clause in MERGE statement must be followed by UPDATE or DELETE clause'，核心原因：

1. MERGE语法限制：WHEN MATCHED分支仅支持绑定UPDATE或DELETE操作，无法直接执行INSERT
2. 重复定义了相同条件的WHEN MATCHED分支，违反语法规范

原错误语句：

merge into target_table as tgt 
using source_table as src
on src.id = tgt.id
when not matched then 
insert (id, value1, value2, end_date, active_flag) values (src.id, src.value1, src.value2, null, 'Y')
when matched and (src.value1 != tgt.value1 or src.value2 != src.value2) then
update set id = src.id, value1 = src.value1, value2 = src.value2, end_date = get_date(), active_flag = 'N'
when matched and (src.value1 != tgt.value1 or src.value2 != src.value2) then
insert (id, value1, value2, end_date, active_flag) values (src.id, src.value1, src.value2, null, 'Y')

正确实现方式 #

方式一：拆分MERGE+INSERT两步执行 #

先通过MERGE处理匹配记录的更新，再通过INSERT插入所有需要新增的记录（包括值不匹配的新记录和无匹配ID的记录）

1. 执行MERGE更新现有记录：

MERGE INTO target_table AS tgt
USING source_table AS src
ON src.id = tgt.id
WHEN MATCHED AND (src.value1 != tgt.value1 OR src.value2 != tgt.value2) THEN
    UPDATE SET 
        end_date = CURRENT_DATE(), -- 根据数据库替换为对应取当日函数，如SQL Server用GETDATE()
        active_flag = 'N';

2. 执行INSERT插入新记录：

INSERT INTO target_table (id, value1, value2, end_date, active_flag)
SELECT 
    src.id,
    src.value1,
    src.value2,
    NULL,
    'Y'
FROM source_table AS src
LEFT JOIN target_table AS tgt
    ON src.id = tgt.id
    AND src.value1 = tgt.value1
    AND src.value2 = tgt.value2
WHERE tgt.id IS NULL;

这里的LEFT JOIN条件确保只插入两种情况：ID不存在的记录，或者ID存在但value1/value2不匹配的记录。

方式二：CTE+合并操作（支持CTE的数据库适用） #

如果数据库支持CTE（如PostgreSQL、SQL Server），可以先标记需要更新的记录，再统一处理：

WITH records_to_update AS (
    SELECT tgt.id
    FROM target_table AS tgt
    JOIN source_table AS src
        ON tgt.id = src.id
    WHERE src.value1 != tgt.value1 OR src.value2 != tgt.value2
)
-- 先更新需要失效的记录
UPDATE target_table
SET end_date = CURRENT_DATE(), active_flag = 'N'
WHERE id IN (SELECT id FROM records_to_update);

-- 再插入新记录
INSERT INTO target_table (id, value1, value2, end_date, active_flag)
SELECT 
    src.id,
    src.value1,
    src.value2,
    NULL,
    'Y'
FROM source_table AS src
WHERE NOT EXISTS (
    SELECT 1 FROM target_table AS tgt
    WHERE tgt.id = src.id
      AND tgt.value1 = src.value1
      AND tgt.value2 = src.value2
      AND tgt.active_flag = 'Y'
);

注意事项 #

• 根据你使用的数据库，替换CURRENT_DATE()为对应取当前日期的函数（如MySQL用CURDATE()）
• 并发写入场景下，建议添加事务保证操作原子性

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