How to Find Duplicate Records in a Table

October 19, 2010

in FAQ, SQL

Duplicate Records in a Table

A usual situation we come across in development: a table missed a unique key and allowed duplicate rows to be entered unchecked.  Now we want to find and delete those duplicates.

In this article, we’ll see how to achieve this.

Consider the table dup_emp with these values:

SQL> select *
  2  from dup_emp
  3  order by empno;

     EMPNO ENAME                 SAL
---------- -------------- ----------
         1 Adam                  400
         2 Sandy                 300
         2 Ted                   450
         3 Mark                  450
         4 Alan                  450
         4 Carol                 200
         4 Peter                 250
         5 David                 350

8 rows selected.

As you can see, there are two entries for empno 2 and three entries for empno 4. We want each empno to correspond to a single employee only.

The query below finds all duplicate records i.e. all empnos with more than one entry in the table:

SQL> select *
  2  from
  3  (select d.*
  4        , count(*) over
  5          (partition by empno) cnt
  6   from dup_emp d
  7  )
  8  where cnt > 1;

     EMPNO ENAME   SAL        CNT
---------- ------ ---- ----------
         2 Sandy   300          2
         2 Ted     450          2
         4 Alan    450          3
         4 Carol   200          3
         4 Peter   250          3

Deleting the duplicate records

The usual need is to delete all but one such row. The first thing is to know the deciding factor -out of the duplicates, which one is to be retained?

The next SQL retains the row with the minimum rowid:

SQL> delete from dup_emp a
  2  where rowid >
  3    (select min(rowid)
  4     from dup_emp b
  5     where a.empno = b.empno);

3 rows deleted.

SQL> select *
  2  from dup_emp;

     EMPNO ENAME   SAL
---------- ------ ----
         1 Adam    400
         2 Sandy   300
         3 Mark    450
         4 Alan    450
         5 David   350

Change the WHERE condition according to your needs.

For Further Reading

Replace Duplicates in ID Column with Unique Values: A solution for scenarios in which the duplicates are not to be deleted – they are to be retained with an altered key value.

{ 5 comments… read them below or add one }

1 Vishal Kolekar August 7, 2011 at 7:45 pm

How to delete most recent rows from table?
or
how to delete oldest row and keep latest row as it is?

2 Madan.P March 14, 2012 at 1:10 pm

Very useful info. Thanks a lot

3 R.Kiran February 11, 2013 at 1:36 pm

Thanks…Very useful.

4 P Kumar February 12, 2014 at 1:59 pm

Useful notes…..

5 shraddha April 22, 2014 at 5:30 pm

very important topic 🙂 good one.

Leave a Comment

Previous post:

Next post: