DELETING DUPLICATE ROWS FROM A ORACLE TABLE.

Collected from :http://rajiboracle.blogspot.com/2013/05/deleting-duplicate-rows-from-table.html

SQL> CREATE TABLE TEST_DUP(ID NUMBER, NAME VARCHAR2(200));

Table created.

SQL> Insert into TEST_DUP
  2     (ID, NAME)
  3   Values
  4     (10, 'RAJIB');

1 row created.

SQL> Insert into TEST_DUP
  2     (ID, NAME)
  3   Values
  4     (10, 'RAJIB');

1 row created.

SQL> Insert into TEST_DUP
  2     (ID, NAME)
  3   Values
  4     (20, 'KING');

1 row created.

SQL> Insert into TEST_DUP
  2     (ID, NAME)
  3   Values
  4     (20, 'RAJIB');

1 row created.

SQL> Insert into TEST_DUP
  2     (ID, NAME)
  3   Values
  4     (30, 'MORGAN');

1 row created.

SQL> Insert into TEST_DUP
  2     (ID, NAME)
  3   Values
  4     (20, 'KING');

1 row created.

SQL> Insert into TEST_DUP
  2     (ID, NAME)
  3   Values
  4     (30, 'MORGAN');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TEST_DUP;

        ID NAME
---------- --------------------
        10 RAJIB
        10 RAJIB
        20 KING
        20 RAJIB
        30 MORGAN
        20 KING
        30 MORGAN

7 rows selected.

SQL> DELETE FROM TEST_DUP
  2  WHERE ROWID NOT IN (SELECT MIN (ROWID)
  3  FROM TEST_DUP
  4  GROUP BY ID,NAME);

3 rows deleted.

SQL> SELECT * FROM TEST_DUP;

        ID NAME
---------- --------------------
        10 RAJIB
        20 KING
        20 RAJIB
        30 MORGAN

SQL> COMMIT;

Commit complete.

SQL>