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

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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!!

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

Latest Posts