Delete duplicate rows from Oracle

There are times when duplicate rows somehow creep into the table. The best scenario to happen this is when the data is to be imported from some other table or data and the Constraints are removed so that data import successfully.

Now in Oracle you can delete the duplicate entries by just executing a simple SQL because Oracle stores an index to each row in a table known as ROWID

SQL> CREATE TABLE duptest
  2    ( Id varchar(5),
  3    nonid varchar(5));

Table created.

SQL> INSERT INTO duptest VALUES('1','a');

1 row created.

SQL> INSERT INTO duptest VALUES('2','b');

1 row created.

SQL> INSERT INTO duptest VALUES('2','b');

1 row created.

SQL> INSERT INTO duptest VALUES('2','b');

1 row created.

SQL> commit;

Commit complete.

SQL> DELETE FROM duptest WHERE rowid NOT IN (SELECT max(rowid) FROM duptest GROUP BY id);

2 rows deleted.

SQL> commit;

Commit complete.

SQL> INSERT INTO duptest VALUES('1','a');

1 row created.

SQL> INSERT INTO duptest VALUES('1','a');

1 row created.

SQL> INSERT INTO duptest VALUES('1','a');

1 row created.

SQL> commit;

Commit complete.

SQL> DELETE FROM duptest
  2  WHERE ROWID IN (SELECT ROWID FROM (SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) rnk
  3                                       FROM   duptest )
  4                  WHERE rnk>1);

3 rows deleted.

SQL> commit;

Commit complete.

Enjoy