如何将DELETE+INSERT逻辑改写为含FLATTEN的MERGE语句？

可行性说明 #

完全可以直接用单条MERGE语句实现你的需求，不需要创建临时表存储待插入数据。

实现语句 #

MERGE INTO FINAL_TABLE AS tgt
USING (
    -- 保留原有的FLATTEN拆分逻辑和过滤条件
    SELECT 
        X, 
        Y.value :: string AS Y_SPLIT, 
        "DATE", 
        "PUBLIC"
    FROM LANDING_TABLE,
         LATERAL FLATTEN (INPUT => STRTOK_TO_ARRAY(LANDING_TABLE.column, ', '), OUTER => TRUE) y
    WHERE "PUBLIC" ILIKE 'TRUE' AND "DATE" >= CURRENT_DATE()
) AS src
-- 匹配目标表中所有需要清理的行（DATE>=当前日期）
ON tgt."DATE" >= CURRENT_DATE()
-- 删除匹配到的旧数据
WHEN MATCHED THEN DELETE
-- 插入源表中符合条件的新数据
WHEN NOT MATCHED THEN INSERT (X, Y_SPLIT, "DATE", "PUBLIC")
VALUES (src.X, src.Y_SPLIT, src."DATE", src."PUBLIC");

逻辑说明 #

这条MERGE语句完全等价于你原有的DELETE+INSERT逻辑：

1. 先删除FINAL_TABLE中所有DATE大于等于当前日期的数据
2. 再将LANDING_TABLE中经过FLATTEN拆分、符合过滤条件的新数据插入到FINAL_TABLE中
3. 自动保留了FINAL_TABLE中DATE小于当前日期的历史数据

注意事项 #

• 确保FINAL_TABLE的列定义与源查询的列顺序、数据类型完全匹配
• 如果FINAL_TABLE存在唯一约束，需提前确认源数据中无重复值，避免插入失败
• 建议先在测试环境验证语句执行结果，确保数据符合预期

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