Deleting duplicate records

In my previous blog I showed how to identify duplicate records. On this blog I will show how to remove duplicate records.

To delete all duplicate with out retaining single record.

Delete from dups

where rowid in

(Select Rowid from

(

select rowid,

Count(First_name)OVer (Partition by First_name, Last_name, Salary ) as Dup_count

from dups

)a where dup_count >1)

To delete duplicate and retain one record from two or many duplicate records:

Delete from dups

where rowid in

(Select Rowid from

(

select rowid,

Count(First_name)OVer (Partition by First_name, Last_name, Salary Order by 1,2,3 rows unbounded preceding) as Dup_count

from dups

)a where dup_count >1)