Delete duplicate rows on table with/without primary key in Sql Server 2005

Posted by Sksingh on 6/7/2011 | Category: Sql Server Interview questions | Views: 8664 | Points: 40

Post |

Interview FAQs |

Exclusive Questions |

Interview Home

Answer:

Here below query that have multiple repeated records, my aim to delete duplicate records and keep one record for each unique row.

select * from employee



insert into employee(empid,empname,salary) 

select 1,'A',20000 union all

select 1,'A',20000 union all

select 1,'A',20000 union all

select 1,'A',20000 union all

select 2,'B',20000 union all

select 1,'C',20000 union all

select 2,'B',40000 



-- delete duplicate records based on name 

set rowcount 1

delete from employee  where ( select count(*) from employee ee where employee.Empname = ee.Empname) > 1



while (@@rowcount > 0)

begin

    delete from employee  where ( select count(*) from employee ee where employee.Empname = ee.Empname) > 1

end 



set rowcount 0