Adsense Ad

Tuesday, 14 March 2017

How to delete duplicate rows from a table

SQL> CREATE TABLE TEST(ID NUMBER(2), NAME VARCHAR2(50));

Table created.

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

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TEST;

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

6 rows selected.

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

3 rows deleted.

SQL> SELECT * FROM TEST;

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

SQL> COMMIT;

Commit complete.

2 comments:

Ferhat said...

Very nice and simple approach

Khalil.Shaikh said...

Good one.