"how to find duplicate records in oracle data base"

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

Comments

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

  • 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
  • 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.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories