'"how to find duplicate records in oracle data base"' Thread RSS Feed

"how to find duplicate records in oracle data base"

Wed, 13 Jul 2005 02:55:12 -0700

I would like to know the oracle (SQL) query to find the duplicate records in a oracle data base.
Kindly help me.
Thanks in advance,
Bye

Re: "how to find duplicate records in oracle data base"

Thu, 05 Jan 2006 12:40:03 -0700

: I would like to know the oracle (SQL) query to find the duplicate records in a oracle data base.
: Kindly help me.
: Thanks in advance,
: Bye
:

By duplicate records I presume you mean records with similar keys?
(Which begs the question as to why your primary key constraint didn't kick out the duplicate entries!)

Try this:-

select *
from table
group by key1, key2, ..., keyN
having count (*) > 1;

If you want to list them for a data cleanup excercise add an order by clause to list them in the order you want.

Gothmordrin
Think it through first - then pester someone.

Re: "how to find duplicate records in oracle data base"

Sat, 21 Feb 2009 23:58:45 -0700

U can find and delete the Duplicate files

delete from test a
where rowid <> ( select max(rowid)
from test b
where a.sno = b.sno
and a.sname = b.sname )

This querry will find duplicate rows and delete those.

Regards,
Pasha

Re: "how to find duplicate records in oracle data base"

Fri, 29 May 2009 08:42:00 -0700

This way worked for me:

SELECT COL_TO_CHECK, COUNT(COL_TO_CHECK)
FROM TABLE_NAME
GROUP BY COL_TO_CHECK
HAVING COUNT(COL_TO_CHECK) > 1

This post has been deleted.

Mon, 14 Feb 2011 09:51:18 -0700

This post has been deleted.