Redshift中WHEN MATCHED子句附加谓词的替代方案

Redshift的MERGE语法不支持在WHEN MATCHED子句后添加额外过滤条件，因此需要将原Snowflake的MERGE拆分为UPDATE和INSERT两个独立语句，实现完全等价的逻辑：

1. 执行UPDATE操作 #

先匹配主键（post_code、location、ad），同时加上原MERGE中WHEN MATCHED后的过滤条件，更新目标表的指定字段：

UPDATE dest
SET speed = src.speed,
    manufacturer = src.manufacturer,
    device_model = src.device_model
FROM src
WHERE src.post_code = dest.post_code
  AND src.location = dest.location
  AND src.ad = dest.ad
  AND src.delivered_on >= dest.delivered_on
  AND src.accuracy > dest.accuracy;

2. 执行INSERT操作 #

插入目标表中不存在匹配主键的源表记录，用NOT EXISTS判断无匹配关系：

INSERT INTO dest (
    delivered_on,
    ad,
    location,
    accuracy,
    speed,
    manufacturer,
    device_model,
    post_code
)
SELECT
    src.delivered_on,
    src.advertiser_id,
    src.location,
    src.accuracy,
    src.speed,
    src.manufacturer,
    src.device_model,
    src.post_code
FROM src
WHERE NOT EXISTS (
    SELECT 1
    FROM dest
    WHERE dest.post_code = src.post_code
      AND dest.location = src.location
      AND dest.ad = src.ad
);

补充说明 #

• 拆分后的语句逻辑与原MERGE完全一致：优先更新符合条件的匹配记录，再插入无匹配的新数据。
• 若需保证操作的原子性，可将两个语句放入同一个事务中执行，避免中间状态被其他会话读取。

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