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

Posted by Krishnamanohar under Sql Server on 4/22/2013 | Points: 10 | Views : 1058 | Status : [Member] | Replies : 4
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




Responses

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

Up
0
Down

Resolved
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: Satyapriyanayak on: 4/22/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
DELETE FROM emp a
WHERE rowid <> ( SELECT max(rowid) FROM emp b
WHERE a.empno = b.empno )


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

If this post helps you mark it as answer
Thanks

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

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

Up
0
Down

/*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

Up
0
Down


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

Login to post response