Delete Duplicate Records

Thanks Bhavani,, But here is the problem that i am using SQL Server 2000 in which row_number() function is not available.

Do you have any way to do it in SQL Server 2000

 
  
Declare @tbl Table(name varchar(100), number int,id int)
insert into  @tbl 
select 'divya',111,1 union all 
select 'divya',211,1 union all 
select 'divya',311,1 union all 
select 'srinu',111,2 union all 
select 'srinu',211,2 union all 
select 'srinu',311,2 

set  rowcount  1

while exists ( select id from @tbl group by id having count(*) > 1)

begin

	delete from @tbl where id in(select top 1 id from @tbl group by id having count(*) > 1) 
end

set rowcount  0

select * from  @tbl


 There can be other method by adding an identity column

Declare @tbl Table(name varchar(100), number int,id int)
insert into  @tbl 
select 'divya',111,1 union all 
select 'divya',211,1 union all 
select 'divya',311,1 union all 
select 'srinu',111,2 union all 
select 'srinu',211,2 union all 
select 'srinu',311,2 

set  rowcount 1
 

while exists ( select id from @tbl group by id having count(*) > 1 )

begin
	delete from @tbl where id in(select top 1 id from @tbl group by id having count(*) > 1) 
end

set rowcount  0

select * from  @tbl

Another method can be by adding any identity column.

Yes take data in any table variable using select distinct * and delete all data from orignal table and put distinct data back in the table will work fine for this scenario.

This is the most common question you will in the forums

 

http://www.sql-server-performance.com/articles/dba/delete_duplicates_p1.aspx

http://support.microsoft.com/kb/139444

http://www.sqlteam.com/article/deleting-duplicate-records

 

 

 

 

;

 

with

Dups

as

(

 

 

SELECT *,row_number() OVER (partition by id order by id) as  RowNum FROM  employee

)

Delete 

 

from Dups where rownum > 1 ;

SELECT 

 

* FROM  employee