Search code examples
sqloracle11g

Prevent Duplicate Insert Data in Oracle

i have table COBA with 3 fields, they are NOMER, NAMA, KETERANGAN. in table there are exist data like this:

table COBA

i wanna insert another values to that table, but how can i prevent duplicate insert query data in oracle? i have tried use WHERE NOT EXIST, this is my query:

INSERT INTO COBA(NOMER, NAMA, KETERANGAN)
(SELECT '2','FIAN', 'JEKULO'
FROM DUAL
WHERE NOT EXISTS (SELECT * FROM COBA WHERE NOMER='1' AND NAMA='AMIN' AND KETERANGAN='JEPARA'))

that query didn't work....any suggest for me,...thanks...

Solution

  • If you dont wanna use Unique constraint, you can you left join while inserting to check if the nomer exists in target or not like below. By this method you will not get error even if the record already exists in your table. It will just be skipped.

    insert into coba
(select s.nomer,s.nama,s.ket from 
    (select 1 as nomer,'AA' as nama,'bb' as ket from dual) s
left join 
    coba t
on s.nomer=t.nomer
    where t.nomer is null
);

    I created a fiddle in MySQL (as Oracle is not working) but the functionality would be same. As you can see in example below, the nomer =1 is not inserted again.

    See fiddle demo here

    http://sqlfiddle.com/#!2/3add2/1