[SOLVED] insert record where not exist

insert record where not exist

INSERT INTO CUST_ID_HISTORY(CUST_ID,PREVIOUS_CUST_ID,UPDATE_DATE) 
SELECT CUST_ID,PREVIOUS_CUST_ID,UPDATE_DATE
FROM CUST_ID_HISTORY WHERE NOT EXISTS (SELECT 1 FROM CUST_ID_HISTORY WHERE
CUST_ID='SCB301' AND PREVIOUS_CUST_ID='SCB201');

Above query insert a new record to CUST_ID_HISTORY when inserted value is not exist in the table.I encounter error ORA-00001: unique constraint with the supplied value, but both values are not exist in the table.

Solution

Your statement selects from CUST_ID_HISTORY and inserts into CUST_ID_HISTORY . In other words, when there is no record for where CUST_ID != 'SCB301' and PREVIOUS_CUST_ID != 'SCB201' it tries to create a duplicate record for every record in CUST_ID_HISTORY. Presumably this is the table's primary key, hence the ORA-00001 unique key violation.

If what you're trying to achieve is insert a record into CUST_ID_HISTORY for those values perhaps you need a merge statement. Something like this:

merge into CUST_ID_HISTORY tgt
using ( select 'SCB301' as id, 'SCB201' as prev_id
         from dual ) src
on ( src.id = tgt.cust_id 
      and src.prev_id = tgt.prev_cust_id)
when not matched then
    insert (cust_id, prev_cust_id, update_date)
    values (src.id, src.prev_id, sysdate)
/