防止在 Oracle 中重复插入数据 [英] Prevent Duplicate Insert Data in Oracle

问题描述

我有包含 3 个字段的 COBA 表，它们是 NOMER、NAMA、KETERANGAN.在表中有这样的数据:

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

我想在该表中插入另一个值，但是如何防止在 oracle 中重复插入查询数据?我试过使用 WHERE NOT EXIST，这是我的查询:

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

推荐答案

如果你不想使用 Unique 约束，你可以在插入时 left join 来检查 nomer> 存在于目标中或不如下.通过这种方法，即使记录已存在于您的表中，您也不会出错.它将被跳过.

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

我在 MySQL 中创建了一个小提琴(因为 Oracle 不工作)但功能是相同的.正如您在下面的示例中所见，nomer =1 不会再次插入.

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

这篇关于防止在 Oracle 中重复插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！