Merge语句双向对比：目标表未匹配主键标记closed的实现问题

源表与目标表结构及数据 #

源表 source_after #

create table source_after (
    binary_path varchar2(40),
    hostname varchar2(40),
    change_column varchar2(40),
    flag varchar2(20) default 'open'
);

insert all 
into source_after (binary_path,hostname,change_column) values ('java','b','DMZ') 
into source_after (binary_path,hostname,change_column) values ('apache','c','drn')
into source_after (binary_path,hostname,change_column) values ('NEW','NEW','NEW')
select * from dual;

数据内容：

目标表 destination #

create table destination (
binary_path varchar2(40),
hostname varchar2(40),
change_column varchar2(40),
flag varchar2(20) 
);

insert all 
into destination (binary_path,hostname,change_column) values ('python','a','drn') 
into destination (binary_path,hostname,change_column) values ('java','b','drn') 
into destination (binary_path,hostname,change_column) values ('apache','c','drn')
into destination (binary_path,hostname,change_column) values ('spark','d','drn')
select * from dual;

数据内容：

两张表的组合主键为 (binary_path, hostname)

同步需求 #

• 若destination中的主键在source_after存在：更新destination的change_column为source_after对应值，同步flag为open
• 若destination中的主键在source_after不存在：将该条数据的flag标记为closed
• 若source_after中的主键在destination不存在：将该条数据插入destination

现有Merge语句的不足 #

原语句仅实现了需求1和3，无法标记destination中未匹配的主键数据为closed：

merge into destination d
using (select * from source_after) s on (d.hostname = s.hostname and d.binary_path = s.binary_path)
when matched then update
set 
d.change_column = s.change_column,
d.flag = s.flag
when not matched then insert 
(d.binary_path,d.hostname,d.change_column,d.flag)
values
(s.binary_path,s.hostname,s.change_column,s.flag)
;

执行后未满足需求2，python和spark的flag仍为null。

解决方案 #

方法1：分两步执行（简洁易维护） #

1. 先将目标表所有数据标记为closed

UPDATE destination SET flag = 'closed';

2. 执行Merge同步源表的新增与更新数据

MERGE INTO destination d
USING (SELECT * FROM source_after) s
ON (d.binary_path = s.binary_path AND d.hostname = s.hostname)
WHEN MATCHED THEN
    UPDATE SET 
        d.change_column = s.change_column,
        d.flag = s.flag
WHEN NOT MATCHED THEN
    INSERT (binary_path, hostname, change_column, flag)
    VALUES (s.binary_path, s.hostname, s.change_column, s.flag);

方法2：单次Merge完成（适合需原子操作的场景） #

通过构造包含目标表全量主键和源表数据的虚拟数据集，一次性完成所有操作：

MERGE INTO destination d
USING (
    -- 处理目标表已存在的记录：标记未匹配为closed，更新匹配记录
    SELECT 
        d.binary_path,
        d.hostname,
        s.change_column,
        CASE WHEN s.binary_path IS NOT NULL THEN s.flag ELSE 'closed' END AS flag,
        1 AS match_type
    FROM destination d
    LEFT JOIN source_after s 
        ON d.binary_path = s.binary_path AND d.hostname = s.hostname
    UNION ALL
    -- 处理源表新增的记录
    SELECT 
        s.binary_path,
        s.hostname,
        s.change_column,
        s.flag,
        2 AS match_type
    FROM source_after s
    WHERE NOT EXISTS (
        SELECT 1 FROM destination d 
        WHERE d.binary_path = s.binary_path AND d.hostname = s.hostname
    )
) t
ON (d.binary_path = t.binary_path AND d.hostname = t.hostname)
WHEN MATCHED THEN
    UPDATE SET 
        d.change_column = COALESCE(t.change_column, d.change_column),
        d.flag = t.flag
WHEN NOT MATCHED THEN
    INSERT (binary_path, hostname, change_column, flag)
    VALUES (t.binary_path, t.hostname, t.change_column, t.flag);

预期执行结果 #

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