how to delete to duplicate rows of a table? [Resolved]

Posted by Krishnamanohar under Sql Server on 4/22/2013 | Points: 10 | Views : 2156 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi experts,
How to deelete the duplicate rows of a table ?what is best way to achieve this ? what is recommended way?
Thanks in Advance
Krish

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Kirthiga on: 4/24/2013 [Member] Starter | Points: 50

0	Hi Krishnamanohar, The best way to delete duplicate rows is by using CTE See below the Example code create table Test(SNo int identity, Name varchar(10)) insert into Test values('AAA'),('BBB'),('BBB'),('CCC'),('CCC'),('DDD') select * from Test --Query to delete duplication rows ;with dup as (select ,ROW_NUMBER()over(partition by Name order by Name)DupNo from Test )delete from dup where DupNo>1 Krishnamanohar, if this helps please login to Mark As Answer*. \| Alert Moderator

Hi Krishnamanohar,

The best way to delete duplicate rows is by using CTE

See below the Example code

create table Test(SNo int identity, Name varchar(10))

insert into Test values('AAA'),('BBB'),('BBB'),('CCC'),('CCC'),('DDD')



select * from Test



--Query to delete duplication rows



;with dup as

(select *,ROW_NUMBER()over(partition by Name order by Name)DupNo from Test

)delete from dup where DupNo>1

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

Posted by: Learningtorise on: 4/23/2013 [Member] Starter | Points: 25

0	/My Query to Get Duplicate Rows containing Employee Name from Employee Table/ SELECT EmpID, EmpName, Count() FROM Employee HAVING Count() > 1 GROUP BY EmpID, EmpName ;-) http://hashtagakash.wordpress.com/ Krishnamanohar, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sriramnandha on: 5/11/2013 [Member] Starter | Points: 25

0	DELETE FROM EMPLOYEE WHERE EMPID NOT INT (SELECT EMPID FROM EMPLOYEE GROUP BY EMPID HAVING COUNT()<1) sriram Krishnamanohar, if this helps please login to Mark As Answer*. \| Alert Moderator

Latest Posts