SQLTeam.com | Weblogs | Forums

Remove duplicate entries from tables


How to remove duplicate data from tables. Each table has a number of relation tables and a number of supporting view and dashboard.

The tables are used in live environment. Is there a solution to the problem


Please explain how to define if rows are duplicate.

The following shows a table where the first two column "ought" to be unique, and thus:
col1=4, col2='D' has two duplicates
col1=6, col2='F' has one duplicate

create table dup_table(col1 int,col2 varchar(10),col3 date);

insert into dup_table values

select * from dup_table;

delete a
  from (select row_number() over(partition by col1,col2 order by col3 desc) as rn
          from dup_table
       ) as a
 where rn>1

select * from dup_table;

drop table dup_table;