如何修改INSERT语句，避免向指定表插入重复的三列组合记录

现有如下INSERT查询语句：

INSERT INTO FCC_CS_WL_SOURCE_REQUEST_ID_MAP
(
"N_WL_SOURCE_REQUEST_ID",
"V_SOURCE_REQUEST_ID",
"V_TARGET_KEY",
"V_TARGET_INDEXNAME"
)
SELECT 
MAP_SEQ_TEST.nextval,
FCC_CUST_DIM.V_ALT_CUST_ID  AS "V_SOURCE_REQUEST_ID",
FCC_CS_MATCHED_RESULT_BULK.V_TARGET_KEY ,
FCC_CS_MATCHED_RESULT_BULK.V_TARGET_INDEXNAME 
FROM FCC_CS_MATCHED_RESULT_BULK INNER JOIN FCC_CUST_DIM 
ON FCC_CS_MATCHED_RESULT_BULK.V_SOURCE_KEY =FCC_CUST_DIM.V_CUST_INTRL_ID 
AND FCC_CUST_DIM.F_LRI_FL ='Y'
AND FCC_CUST_DIM.V_ALT_CUST_ID  IS NOT NULL 
AND FCC_CS_MATCHED_RESULT_BULK.N_RUN_SKEY =290

需要实现：当V_SOURCE_REQUEST_ID、V_TARGET_KEY、V_TARGET_INDEXNAME三列的组合值已存在于FCC_CS_WL_SOURCE_REQUEST_ID_MAP表中时，阻止插入对应数据。

解决方案1：用NOT EXISTS过滤重复数据 #

直接在原查询的SELECT子句后添加NOT EXISTS条件，筛选出目标表中不存在的组合数据：

INSERT INTO FCC_CS_WL_SOURCE_REQUEST_ID_MAP
(
"N_WL_SOURCE_REQUEST_ID",
"V_SOURCE_REQUEST_ID",
"V_TARGET_KEY",
"V_TARGET_INDEXNAME"
)
SELECT 
MAP_SEQ_TEST.nextval,
FCC_CUST_DIM.V_ALT_CUST_ID  AS "V_SOURCE_REQUEST_ID",
FCC_CS_MATCHED_RESULT_BULK.V_TARGET_KEY ,
FCC_CS_MATCHED_RESULT_BULK.V_TARGET_INDEXNAME 
FROM FCC_CS_MATCHED_RESULT_BULK INNER JOIN FCC_CUST_DIM 
ON FCC_CS_MATCHED_RESULT_BULK.V_SOURCE_KEY =FCC_CUST_DIM.V_CUST_INTRL_ID 
AND FCC_CUST_DIM.F_LRI_FL ='Y'
AND FCC_CUST_DIM.V_ALT_CUST_ID  IS NOT NULL 
AND FCC_CS_MATCHED_RESULT_BULK.N_RUN_SKEY =290
-- 过滤已存在的三列组合数据
WHERE NOT EXISTS (
    SELECT 1 FROM FCC_CS_WL_SOURCE_REQUEST_ID_MAP t
    WHERE t."V_SOURCE_REQUEST_ID" = FCC_CUST_DIM.V_ALT_CUST_ID
      AND t."V_TARGET_KEY" = FCC_CS_MATCHED_RESULT_BULK.V_TARGET_KEY
      AND t."V_TARGET_INDEXNAME" = FCC_CS_MATCHED_RESULT_BULK.V_TARGET_INDEXNAME
);

解决方案2：Oracle专属MERGE语句（更灵活） #

如果后续需要扩展逻辑（比如存在时更新、不存在时插入），可以使用Oracle的MERGE语句，仅实现阻止插入重复数据的写法如下：

MERGE INTO FCC_CS_WL_SOURCE_REQUEST_ID_MAP tgt
USING (
    SELECT 
        FCC_CUST_DIM.V_ALT_CUST_ID  AS "V_SOURCE_REQUEST_ID",
        FCC_CS_MATCHED_RESULT_BULK.V_TARGET_KEY ,
        FCC_CS_MATCHED_RESULT_BULK.V_TARGET_INDEXNAME 
    FROM FCC_CS_MATCHED_RESULT_BULK INNER JOIN FCC_CUST_DIM 
    ON FCC_CS_MATCHED_RESULT_BULK.V_SOURCE_KEY =FCC_CUST_DIM.V_CUST_INTRL_ID 
    AND FCC_CUST_DIM.F_LRI_FL ='Y'
    AND FCC_CUST_DIM.V_ALT_CUST_ID  IS NOT NULL 
    AND FCC_CS_MATCHED_RESULT_BULK.N_RUN_SKEY =290
) src
ON (
    tgt."V_SOURCE_REQUEST_ID" = src."V_SOURCE_REQUEST_ID"
    AND tgt."V_TARGET_KEY" = src."V_TARGET_KEY"
    AND tgt."V_TARGET_INDEXNAME" = src."V_TARGET_INDEXNAME"
)
WHEN NOT MATCHED THEN
INSERT (
    "N_WL_SOURCE_REQUEST_ID",
    "V_SOURCE_REQUEST_ID",
    "V_TARGET_KEY",
    "V_TARGET_INDEXNAME"
)
VALUES (
    MAP_SEQ_TEST.nextval,
    src."V_SOURCE_REQUEST_ID",
    src."V_TARGET_KEY",
    src."V_TARGET_INDEXNAME"
);

额外保障：创建唯一约束 #

为了从根源避免重复数据，建议给目标表创建唯一约束，确保这三列的组合不会重复：

ALTER TABLE FCC_CS_WL_SOURCE_REQUEST_ID_MAP
ADD CONSTRAINT UK_WL_SOURCE_REQUEST_COMBO UNIQUE (
    "V_SOURCE_REQUEST_ID",
    "V_TARGET_KEY",
    "V_TARGET_INDEXNAME"
);

这样即使插入语句未做过滤，数据库也会直接报错阻止重复插入，双重保障数据唯一性。

内容的提问来源于stack exchange，提问作者Shanij P.S