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

Question

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 .?

Do you want to stop the whole INSERT, or just skip the duplicate rows? — jarlh, Nov 25 '22 at 13:43
BTW, why do you copy data between tables? Can't you just create a view, it will always return up-to-date data. — jarlh, Nov 25 '22 at 13:53
Does this answer your question? [Prevent Duplicate Insert Data in Oracle](https://stackoverflow.com/questions/34645765/prevent-duplicate-insert-data-in-oracle) — astentx, Nov 25 '22 at 14:29

score 0 · Answer 1 · answered Nov 25 '22 at 13:44

0

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 )

answered Nov 25 '22 at 13:44

SuperPoney

563
6
21

My query return more rows with same valued columns at the first run itself (means V_SOURCE_REQUEST_ID,V_TARGET_KEY,V_TARGET_INDEXNAME pair is duplicated). In that case it inserts multiple rows. How to prevent that by returning only distinct values (any distinct or group by logic works here?) – Shanij P.S Nov 25 '22 at 14:35
Just add DISTINCT to the SELECT query – SuperPoney Nov 28 '22 at 09:01
That wont work if you have sequence in the select statement – Shanij P.S Nov 28 '22 at 12:39

score 0 · Accepted Answer · answered Nov 25 '22 at 13:57

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
  );

My query return more rows with same valued columns at the first run itself (means V_SOURCE_REQUEST_ID,V_TARGET_KEY,V_TARGET_INDEXNAME pair is duplicated). In that case it inserts multiple rows. How to prevent that by returning only distinct values (any distinct or group by logic works here?) — Shanij P.S, Nov 25 '22 at 14:35

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

2 Answers2