Home » SQL & PL/SQL » SQL & PL/SQL » How to retrieve duplicate records from a table [merged]
How to retrieve duplicate records from a table [merged] [message #414224] Tue, 21 July 2009 01:53 Go to next message
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
Member
Hi,
My requirement is that, for a table initially primaryley
is disabled.
Then we insert duplicate records in to that table.
Then i enable the primary key on that table.
Now , my query is how can i retrieve any single record
from those duplicate records based on primary key column.
i need to retrieve any one record either first or last record.

I am using following table for test purpose.

desc pr_test;

Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(10)
NAME VARCHAR2(10)

select * from pr_test;

ID NAME
----- ----------
20 abc
10 pqr
20 xyz
In this table id is primary key.
There are two 20 in id column.
Now i need to get only one record for 20 of id.

Hoping for a quick solution.

Thanks in advance,
Ravi.
Re: How to retrieve single row from duplicate rows [message #414230 is a reply to message #414224] Tue, 21 July 2009 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you insert duplicates if you want to remove them after?

Duplicates questions are the most FAQ, please search BEFORE posting.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: How to retrieve single row from duplicate rows [message #414236 is a reply to message #414224] Tue, 21 July 2009 02:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I guess that when you say
Quote:
Then i enable the primary key on that table
you mean
Quote:
Then I tried to enable the primary key and got a whacking great ORA-00001 error


If you've got these records:
ID NAME
----- ----------
20 abc
10 pqr
20 xyz
then which of the ID=20 records do you want to get back?
Re: How to retrieve single row from duplicate rows [message #414237 is a reply to message #414236] Tue, 21 July 2009 02:30 Go to previous messageGo to next message
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
Member
I did not get any error.
I have enabled the primary key using

alter table pr_test add constraint pr_key1 primary key (id)
DEFERRABLE initially immediate enable novalidate;
statement.

I need to retrieve the first record.
Re: How to retrieve single row from duplicate rows [message #414238 is a reply to message #414224] Tue, 21 July 2009 02:38 Go to previous messageGo to next message
pynbiang
Messages: 1
Registered: July 2009
Location: Shillong
Junior Member
You can use the ROWID. every record in oracle table has a ROWID.
eg.
SELECT ROWID from tablename where primarykey=value;
now having the ROWID you can query like this
SELECT * from tablename where ROWID='AAAMkcAAFAAADfMAAA'.(eg)
You can also use the primary key along with ROWID.
Re: How to retrieve single row from duplicate rows [message #414239 is a reply to message #414237] Tue, 21 July 2009 02:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
I need to retrieve the first record.

There is no such thing as a first record in a relational database.
If you've got another column that we can order the records by, we can get you the first record from that order, but otherwise you're just getting a record back at random.
Re: How to retrieve single row from duplicate rows [message #414241 is a reply to message #414239] Tue, 21 July 2009 02:47 Go to previous messageGo to next message
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
Member
Any one record is enough.
Since in our project duplicate records
will have the same data in all columns.
Re: How to retrieve single row from duplicate rows [message #414244 is a reply to message #414241] Tue, 21 July 2009 02:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Since in our project duplicate records
will have the same data in all columns.


Ah - so you managed to get your test case wrong despite only having 3 rows of data in it....

DELETE <table>
WHERE ROWID NOT IN (SELECT min(rowid)
                    FROM   <table>
                    GROUP BY <pk_column>

Re: How to retrieve single row from duplicate rows [message #414252 is a reply to message #414224] Tue, 21 July 2009 03:54 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
I really really advise you not do what you're doing.
Have a look at this thread for one reason why it's a very bad idea:
http://www.orafaq.com/forum/t/146399/129190/
Re: How to retrieve single row from duplicate rows [message #414480 is a reply to message #414252] Wed, 22 July 2009 05:53 Go to previous messageGo to next message
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
Member
Now my requirement is changed.

I insert million records in to a table.
Then i enable the primarykey constraint.
Now i need to select all the duplicate records based
on primary key column, and i nned to insert them
in to a seperate table.
Then i need to delete all the duplicate records from the table.

How can i do this.

Thanks in Advance,
Ravi.
How to retrieve duplicate records from a table [message #414485 is a reply to message #414224] Wed, 22 July 2009 06:09 Go to previous messageGo to next message
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
Member
Hi,
I insert million records in to a table,
of them very few are duplicate records.
Then i enable the primary key constraint.
Now i need to select all the duplicate records based
on primary key column, and i need to insert them
in to a separate table.
Then i need to delete all the duplicate records from the table.

How can i do this.

Hoping for a quick reply.

Thanks in Advance,
Ravi.
Re: How to retrieve duplicate records from a table [message #414493 is a reply to message #414485] Wed, 22 July 2009 06:23 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is a FAQ; search for it on OraFAQ.
Re: How to retrieve duplicate records from a table [message #414497 is a reply to message #414485] Wed, 22 July 2009 06:29 Go to previous messageGo to next message
elaiyavel
Messages: 114
Registered: April 2008
Senior Member
select * from Table_A where rowid not in
(select max(rowid) from Table_A);
Re: How to retrieve duplicate records from a table [message #414507 is a reply to message #414497] Wed, 22 July 2009 06:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There you get at most one row... whatever is in the table.
I hope you will not try a delete base on this.

Regards
Michel

[Updated on: Wed, 22 July 2009 06:52]

Report message to a moderator

Re: How to retrieve duplicate records from a table [message #414509 is a reply to message #414507] Wed, 22 July 2009 07:02 Go to previous messageGo to next message
elaiyavel
Messages: 114
Registered: April 2008
Senior Member
NOT in clause is used..

Hence it will show up all rows except the row to be retained.. and also verified tehy results.
Re: How to retrieve duplicate records from a table [message #414516 is a reply to message #414509] Wed, 22 July 2009 07:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Hence it will show up all rows except the row to be retained

Quote:
There you get at most one row... whatever is in the table.

Regards
Michel
Re: How to retrieve duplicate records from a table [message #414521 is a reply to message #414516] Wed, 22 July 2009 07:36 Go to previous messageGo to next message
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
Member
select * from Table_A where rowid not in
(select max(rowid) from Table_A);



this query returning all records except the last record,
but my requirement is, i need duplicate records only.

For example:
Following is my table.
in this ID is primary key
I have inserted following records in to this table.
Now i need records having id 10 and 20.
select * from pr_test;

ID NAME
------ ----------
10 sdcs
10 sdcs
20 sdcs
10 swcs
10 ssdcs
10 ssdcs
10 ssdcs
10 ssdcs
10 ssdcs
10 ssdcs
10 ssdcs

ID NAME
------ ----------
20 wddd
20 wddd
20 wddd
20 wddd
20 wddd
20 wddd
20 wddd
20 wddd
283 sdsd
45 sdf


How to do this.
Re: How to retrieve single row from duplicate rows [message #414526 is a reply to message #414480] Wed, 22 July 2009 07:53 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The requirement makes no sense.

You can't enable the primary key as long as you have duplicate records.

Filter the duplicate records in the insert phase.
Re: How to retrieve duplicate records from a table [message #414527 is a reply to message #414521] Wed, 22 July 2009 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How to do this.

Finding duplicates is still a FAQ, did you try to search something?

Regards
Michel
Re: How to retrieve single row from duplicate rows [message #414529 is a reply to message #414526] Wed, 22 July 2009 08:04 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
ThomasG wrote on Wed, 22 July 2009 13:53
The requirement makes no sense.

You can't enable the primary key as long as you have duplicate records.

Filter the duplicate records in the insert phase.


Strictly speaking you can enable a pk with duplicates present, but it's a monumentally bad idea, as can be seen from the other thread I linked to above.

I agree that the duplicates should be removed at the insert phase.
If it's too late to do that then they should be removed before adding the pk.
Re: How to retrieve duplicate records from a table [merged] [message #415086 is a reply to message #414224] Fri, 24 July 2009 12:42 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
One, your index will not work. The no-validate means to not rebuild it and it will NOT point to both records, it cant. and why for goodness's sake do you use such a strange method, use the merge statement and you can handle duplicates anyway you want.
Previous Topic: Use index
Next Topic: problem in sending email from UTL_SMTP pkg
Goto Forum:
  


Current Time: Wed Dec 07 14:59:36 CST 2016

Total time taken to generate the page: 0.09207 seconds