How to delete duplicate records from a table in sql server?

Posted by Sqldev under Sql Server on 12/7/2012 | Points: 10 | Views : 1064 | Status : [Member] | Replies : 2
Hi,

I have found 2 ways to delete duplicate records from a table in sql server in the below:


http://learnsqltips.blogspot.in/2012/12/how-to-delete-duplicate-records-from.html



Please let me know if anybody else no much better ways!!




Responses

Posted by: Ranjeet_8 on: 12/7/2012 [Member] [MVP] Gold | Points: 25

Up
0
Down

DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)


Sqldev, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sourabh07 on: 12/27/2012 [Member] Starter | Points: 25

Up
0
Down
hi..

use the following query in order to sort out the problem....

it will works even at the time whan all the columns of the table replicates...

Declare @temp table(

id int,
name varchar(10)
)
Insert into @temp (id,name)
select 1,'A'
union
select 2,'B'
union
select 1,'A'
union
select 3,'C'
union
select 2,'B'



Insert into @temp (id,name)
select 1,'A'
union
select 2,'B'
union
select 1,'A'
union
select 3,'C'
union
select 2,'B'


Select * from @temp;

with cust as (
select Row_Number() over (partition by id,name order by id,name ) as rcnt,
id,name from @temp
)delete from cust where rcnt>1;

select * from @temp order by id




Sourabh07

Sqldev, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response