How to prevent insert if same records are present in the table-CodePudding

I have a query

    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

Here I need to prevent the insert into FCC_CS_WL_SOURCE_REQUEST_ID_MAP table if V_SOURCE_REQUEST_ID,V_TARGET_KEY,V_TARGET_INDEXNAME columns values is already available with same value which is going to be inserted

How to modify this query to achieve that .?

CodePudding user response：

Use a MERGE statement:

MERGE INTO FCC_CS_WL_SOURCE_REQUEST_ID_MAP dst
USING (
  SELECT d.V_ALT_CUST_ID,
         b.V_TARGET_KEY ,
         b.V_TARGET_INDEXNAME 
  FROM   FCC_CS_MATCHED_RESULT_BULK b
         INNER JOIN FCC_CUST_DIM d
         ON b.V_SOURCE_KEY = d.V_CUST_INTRL_ID 
  AND    d.F_LRI_FL ='Y'
  AND    d.V_ALT_CUST_ID IS NOT NULL 
  AND    b.N_RUN_SKEY =290
) src
ON (
    src.V_ALT_CUST_ID      = dst.V_SOURCE_REQUEST_ID
AND src.V_TARGET_KEY       = dst.V_TARGET_KEY
AND src.V_TARGET_INDEXNAME = dst.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_ALT_CUST_ID,
    src.V_TARGET_KEY,
    src.V_TARGET_INDEXNAME
  );

CodePudding user response：

Add a NOT EXISTS clause in your SELECT:

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
AND NOT EXISTS (SELECT 1 FROM FCC_CS_WL_SOURCE_REQUEST_ID_MAP t1 WHERE V_SOURCE_REQUEST_ID = FCC_CUST_DIM.V_ALT_CUST_ID AND V_TARGET_KEY = FCC_CS_MATCHED_RESULT_BULK.V_TARGET_KEY AND V_TARGET_INDEXNAME = FCC_CS_MATCHED_RESULT_BULK.V_TARGET_INDEXNAME )