How to Delete Duplicate Rows in Oracle

 Hi Guy's,

Today we will see how to eliminate the duplicate records from a table.When tables are designed initially on test environment in some scenarios the primary key is not define, so in these cases there's is a chance of rows duplication.

SQL> select count(*) from table_name;

  COUNT(*)

---------------

   4814821

SQL> select 

kthscd,

kttrnn,

kttype,

ktdrcd,

ktqty from schema.table_name group by

kthscd,

kttrnn,

kttype,

ktdrcd,

ktqty having count(*)>1;

  

6 rows selected.

 >>>>>>>> We have 6 rows Duplicate here  <<<<<<<<<

  

SQL> delete from schema.table_name where rowid not in (select min(rowid) from schema.table_name group by

kthscd,

kttrnn,

kttype,

ktdrcd,

ktqty);

6 rows deleted.

SQL> select count(*) from table_name

  COUNT(*)

--------------------

   4814815

Happy Reading...