新增jira_key字段后MERGE语句向目标表插入重复数据问题

基础环境信息 #

现有测试表结构与初始化数据如下：

CREATE TABLE test_tab1(
  seq_id NUMBER(10),
  e_id NUMBER(10),
  jira_key varchar2(20),
  stage_code NUMBER(10)
);

INSERT INTO test_tab1 VALUES(1,11,'A',2);
INSERT INTO test_tab1 VALUES(1,12,'B',3);

CREATE SEQUENCE test_tab2_sq;
CREATE TABLE test_tab2(
  seq_id NUMBER(10),
  e_id NUMBER(10),
  jira_key varchar2(20),
  stage_code NUMBER(10),
  start_date DATE,
  end_date DATE
);

业务加载规则 #

数据从test_tab1同步到test_tab2需满足以下规则：

• 若test_tab1中某条记录stage_code为3，则test_tab2中对应该e_id需生成stage_code从0到3共4条记录；其中stage_code小于原始stage_code的记录start_date、end_date均赋值为SYSDATE，stage_code等于原始stage_code的记录end_date赋值为NULL。预期输出参考：

• 若某条记录的stage_code下调，需将对应超出新stage_code范围的旧记录的start_date、end_date置为NULL。

故障现象 #

执行下述MERGE语句时，即使e_id对应数据已经完整加载到目标表，仍会重复插入相同条目：

MERGE INTO test_tab2 dst USING(
  WITH  got_new_code  AS
      (
        SELECT m.e_id,m.jira_key
        ,      m.stage_code
        ,      c.code
        FROM  test_tab1 m
        CROSS APPLY (
                    SELECT LEVEL - 1 AS code
                FROM   dual
                CONNECT BY LEVEL <= m.stage_code + 1
                  )      c
      )
      SELECT   *
      FROM     got_new_code n
      FULL JOIN test_tab2 t USING (e_id,jira_key,stage_code))src
      ON (dst.e_id = src.e_id AND dst.stage_code = src.code AND dst.jira_key = src.jira_key)
      WHEN MATCHED THEN UPDATE
SET  dst.start_date = CASE
                  WHEN dst.stage_code <= src.stage_code
              THEN dst.start_date
              ELSE   NULL
            END
,   dst.end_date  = CASE
                  WHEN dst.stage_code < src.stage_code
              THEN NVL (dst.end_date, SYSDATE)
              ELSE   NULL
            END
  WHERE LNNVL (dst.stage_code < src.stage_code)
  OR   dst.end_date     IS NULL
WHEN NOT MATCHED
THEN INSERT (dst.seq_id,dst.e_id, dst.stage_code, dst.start_date, dst.end_date)
     VALUES (seq_id_sq.nextval,src.e_id, src.code, SYSDATE,        CASE
                           WHEN src.code < src.stage_code
                           THEN SYSDATE
                           ELSE NULL
                         END
      )
;

问题根因 #

1. 关联逻辑错位：源子查询中FULL JOIN的关联条件是(e_id,jira_key,stage_code)，此处的stage_code是源表的原始阶段值，而MERGE的匹配条件用的是dst.stage_code = src.code（即生成的0-N序列值）。FULL JOIN后，目标表已存在的非最大阶段记录无法匹配到源侧的code值，导致ON条件判断为不匹配，触发重复插入。
2. 序列名引用错误：初始化创建的序列名为test_tab2_sq，INSERT子句中引用的seq_id_sq不存在，执行时会直接抛出对象不存在的错误。
3. 冗余空行干扰：FULL JOIN会将目标表中超出当前阶段范围的旧记录也带入源结果集，这部分记录源侧字段全为NULL，会触发无意义的插入逻辑。

修复后代码 #

MERGE INTO test_tab2 dst 
USING(
  -- 生成源表对应的全量0~max_stage阶段记录
  SELECT 
    m.e_id,
    m.jira_key,
    m.stage_code AS max_stage,
    c.code AS target_stage
  FROM test_tab1 m
  CROSS APPLY (
    SELECT LEVEL - 1 AS code
    FROM dual
    CONNECT BY LEVEL <= m.stage_code + 1
  ) c
  UNION ALL
  -- 补全目标表中超出当前阶段范围的旧记录，用于后续置空
  SELECT 
    t.e_id,
    t.jira_key,
    NULL AS max_stage,
    t.stage_code AS target_stage
  FROM test_tab2 t
  WHERE NOT EXISTS (
    SELECT 1 FROM test_tab1 m 
    WHERE m.e_id = t.e_id 
      AND m.jira_key = t.jira_key
      AND m.stage_code >= t.stage_code
  )
) src
ON (
  dst.e_id = src.e_id 
  AND dst.stage_code = src.target_stage 
  AND dst.jira_key = src.jira_key
)
WHEN MATCHED THEN UPDATE
SET 
  dst.start_date = CASE
                    WHEN src.max_stage IS NOT NULL AND dst.stage_code <= src.max_stage THEN dst.start_date
                    ELSE NULL
                  END,
  dst.end_date = CASE
                  WHEN src.max_stage IS NOT NULL AND dst.stage_code < src.max_stage THEN NVL(dst.end_date, SYSDATE)
                  ELSE NULL
                END
WHERE 
  src.max_stage IS NULL
  OR LNNVL(dst.stage_code < src.max_stage)
  OR dst.end_date IS NULL
WHEN NOT MATCHED THEN INSERT (
  dst.seq_id,
  dst.e_id, 
  dst.stage_code, 
  dst.jira_key,
  dst.start_date, 
  dst.end_date
)
VALUES (
  test_tab2_sq.nextval,
  src.e_id, 
  src.target_stage, 
  src.jira_key,
  SYSDATE,
  CASE
    WHEN src.target_stage < src.max_stage THEN SYSDATE
    ELSE NULL
  END
);

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