How to Delete the duplicate records

How delete the duplicate rows ?

 Ans:-

1.        Use Rowid with  Group by clause to delete duplicate rows

        Delete from emp

where rowid not in

(select max(rowid)  from emp group by empno);

2.       Use self-join to delete duplicate rows

  Delete from emp A

where rowid not in

                                                                (select max(rowid) from emp B   where A.empno = B.empno );

3.       Use row_number() to delete Duplicate  rows

Delete from emp where rowid in
           Delete from emp where rowid in

                       (  select RID from

                               ( select rowid RID,  row_number() over(partition by empno order by empno) rn  from emp )

                                                                                                                                                                                             where rn > 1 );

                                                   

4.       Use dense_rank() to delete duplicate rows

Delete from emp  where rowid  in

             (

                 select  RID from

                                     (   Select rowid RID ,  dense_rank() over (partition by empno order by rowid ) rn From emp )

                                                                                                                                                                                  where rn > 1 );

5.       Use rank() to delete duplicate rows

Delete from emp  where rowid  in

         (select  RID from     

                                      ( Select rowid RID ,  rank() over (partition by empno order by rowid ) rn  From emp )

where rn > 1 );

6.       Creating New table with distinct record

Step 1 create table table_name2 as select distinct * from table_name1;

Step 2 drop table table_name1;

Step 3 rename table_name2 to table_name1;

Tips: - Always take extra caution while deleting records. 

1. First identify the duplicates using select.

2. Double verify those are actual ‘duplicates’ or not

3. Take backup if necessary

4. Apply commit only if you are sure.

Watch PLSQL Interview Q&A videos
https://www.youtube.com/user/rameshwargupta1/videos