Mir Sayeed Hassan – Oracle Blog

How to delete duplicate rows from the table in oracle database

Now create new table & insert some records init with duplicate:

SQL> create table test_del_dup_rows(eno number(20),name varchar2(20));
Table created.

SQL> insert into test_del_dup_rows values(1,'mir');
1 row created.

SQL> insert into test_del_dup_rows values(2,'syed');
1 row created.

SQL> insert into test_del_dup_rows values(3,'mir');
1 row created.

SQL> insert into test_del_dup_rows values(4,'hassan');
1 row created.

SQL> insert into test_del_dup_rows values(5,'mir');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from test_del_dup_rows;

ENO NAME
---------- --------------------
1 mir
2 syed
3 mir
4 hassan
5 mir

SQL> delete from test_del_dup_rows where ROWID NOT IN (SELECT min(ROWID) from test_del_dup_rows group by name);
2 rows deleted.

Verify

SQL> select * from test_del_dup_rows;

ENO NAME
---------- --------------------
1 mir
2 syed
4 hassan

==========Hence tested & verified in our test db===========