Vivek Agarwal’s Portal/Java Blog

An IBM Gold Consultant’s weblog about IBM, Lotus, WebSphere, J2EE, IT Processes, and other IT technologies

How to detect a duplicate row in an Oracle database table?

Posted by Vivek Agarwal on July 9, 2007


Recently I ran into the situation where I knew that an Oracle database table had a duplicate row, and needed to detect the duplicate row so that I could resolve the situation. You might say that the database design could and should enforce a uniqueness constraint to prevent this situation and you would be right – however given the situation how do you detect the duplicate row?

I don’t claim to be an expert in SQL or Oracle as many can attest but I can perform Google searches! 🙂 Based on a quick search, I found that an effective way to detect duplicate rows is to join a table to itself.

SELECT

    ID,

    DN

FROM

    ldap_person A

WHERE

    rowid >

    (SELECT min(rowid) FROM ldap_person B

        WHERE

        B.dn = A.dn

    );

 

Please note that the where clause of the embedded select statement must specify all columns that make the row a duplicate. Once you have found the duplicate row, you can take the appropriate corrective action whether that is to delete the duplicate row or to update the row to resolve the duplicate.

Advertisements

Sorry, the comment form is closed at this time.

 
%d bloggers like this: