CODEWITHSUNDEEP
sqloracle11g
aminvincent
aminvincent

Reputation: 603

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

Upvotes: 1

Views: 6413

Answers (3)

MT0
MT0

Reputation: 167962

Use a MERGE statement (which will only query the source and destination tables once):

MERGE INTO COBA dst
USING (
  SELECT '2' AS nomer, 'FIAN' AS nama, 'JEKULO' AS keterangan FROM DUAL 
) src
ON (
    dst.nomer      = src.nomer
AND dst.nama       = src.nama
AND dst.keterangan = src.keterangan
)
WHEN NOT MATCHED THEN
  INSERT (NOMER, NAMA, KETERANGAN)
  VALUES (src.nomer, src.nama, src.keterangan);

Upvotes: 0

Utsav
Utsav

Reputation: 8093

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Use a unique constraint:

ALTER TABLE COBA ADD CONSTRAINT uni_c UNIQUE (NOMER, NAMA, KETERANGAN)

Upvotes: 3

Related Questions