How to avoid duplicate rows from being inserted in the table. [message #436004] |
Mon, 21 December 2009 00:51  |
vicks_vj@yahoo.com
Messages: 3 Registered: December 2009
|
Junior Member |
|
|
Hi all,
I have one table in which I want to restrict some records from being inserted. I don't want to put any checked constraints. e.g. consider following table
transaction(
id number primary key,
txn_date timestamp(7),
payee varchar2(40),
amount number,
memo varchar2(40),
ref_num number
)
I want to write SQL which should not inset duplicate record.
e.g.
I have written one as bellow:
insert into transaction
select 1, to_date('2009-12-12','YYYY-MM-DD'), 'Payee1', 12, 'Test', 212 from dual where
(select count(*) from transaction where txn_date=to_date('2009-12-12','YYYY-MM-DD') and
payee='Payee1' and amount=12)=0;
Can I use exists/not exists, which query will be more appropriate. (Please consider that fields which I am using to filter out the duplicate transactions does not contain primary key.)
Can I write such SQL. Or do i check for duplicate rows one by one and then filter the duplicate records.
Suggestions Plz...
Edit/Delete Message
|
|
|
|
|
|
|
|
|
|
|
Re: How to avoid duplicate rows from being inserted in the table. [message #436025 is a reply to message #436015] |
Mon, 21 December 2009 03:00   |
vicks_vj@yahoo.com
Messages: 3 Registered: December 2009
|
Junior Member |
|
|
Michel,
I am not creating any constraint on this table because, I want to check for unique records only in certain operations. For others, I don't care if it inserts duplicate records.
The information inserted in the table is user specific. Nobody else is allowed to insert/update somebody else' information. So I guess, session should not be concern here.
Will the following query work correctly?
insert into transaction
select 1, to_date('2009-12-12','YYYY-MM-DD'), 'Payee1', 12, 'Test', 212 from dual where
not exists (select 1 from transaction where txn_date=to_date('2009-12-12','YYYY-MM-DD') and
payee='Payee1' and amount=12);
|
|
|
|
Re: How to avoid duplicate rows from being inserted in the table. [message #436097 is a reply to message #436025] |
Mon, 21 December 2009 08:42   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
You could very well create a unique index assuming the table is having a primary key and cater only for this particular scenario.
I will leave it to you find out how to do it. As others have already mentioned unique index is the only way to be certain that you don't end up with duplicate values. In software programming always expect the unexpected.
Regards
Raj
|
|
|
|
|
|
|
|
|
|
|
Re: How to avoid duplicate rows from being inserted in the table. [message #514766 is a reply to message #514765] |
Wed, 06 July 2011 07:02   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
One way:
SQL> create table t (id integer, status varchar2(10));
Table created.
SQL> create unique index t_i on t (decode(status,'PENDED',null,id), nullif(status,'PENDED'));
Index created.
SQL> insert into t values(1,'ACTIVE');
1 row created.
SQL> /
insert into t values(1,'ACTIVE')
*
ERROR at line 1:
ORA-00001: unique constraint (MICHEL.T_I) violated
SQL> insert into t values(1,'PENDED');
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> select * from t;
ID STATUS
---------- ----------
1 ACTIVE
1 PENDED
1 PENDED
1 PENDED
4 rows selected.
Regards
Michel
[Updated on: Wed, 06 July 2011 07:03] Report message to a moderator
|
|
|
|