Home » RDBMS Server » Server Administration » how to detect and remove the duplicate records
how to detect and remove the duplicate records [message #372652] Fri, 02 March 2001 05:47 Go to next message
MOTURI PRASAD
Messages: 1
Registered: March 2001
Junior Member
Hi. Whenever importing .dmp file to the application, I have observed that some duplicate records are imported. I want to know, how to find and remove them.

Report message to a moderator

Re: how to detect and remove the duplicate records [message #372654 is a reply to message #372652] Fri, 02 March 2001 07:17 Go to previous messageGo to next message
Joachim Lindner
Messages: 30
Registered: February 2001
Member
Hi Moturi,
in my opinion the question should be how to prevent dups from being created. You can achieve that by dropping or at least cleaning (delete, trumcate) the target table(s) before you invoke IMP. If you omit that the create table statement in the dump will fail but in case IGNORE=Y subsequent row insertion will succeed and create duplicates if there are no constraints (pk, unique) defined that would reject dups.

Anyhow, duplicates can be identified by a statement like ...

select col1, col2, ..., colN
from tab
group by col1, col2, ..., colN
having count(*) > 1;

Report message to a moderator

Re: how to detect and remove the duplicate records [message #372660 is a reply to message #372652] Fri, 02 March 2001 09:48 Go to previous message
ramu
Messages: 82
Registered: February 2001
Member
Try this query..

SELECT *FROM table_name A WHERE A.ROWID >(SELECT MIN(B.ROWID FROM table_name B WHERE A.column_name=B.column_name);

and you can delete by

DELETE FROM table_name A WHERE A.ROWID>(SELECT MIN(B.ROWID FROM table_name B WHERE A.column_name=B.column_name);

Thanks..

Ramu..

Report message to a moderator

Previous Topic: not getting oracle
Next Topic: Julian date
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Jun 29 08:00:35 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.