How to delete duplicate records ? [Resolved]

Posted by Kirthiga under Sql Server on 6/25/2012 | Points: 10 | Views : 1921 | Status : [Member] | Replies : 6
How to delete duplicate records in sql server without creating a temporary table ?

In my table im having 6000 records. Some rows are repeated two or three times.
I want to delete those records without transfering them into another table.




Responses

Posted by: CGN007 on: 6/25/2012 [Member] Silver | Points: 50

Up
0
Down

Resolved
WITH employeeCTE AS 


(

SELECT ROW_NUMBER() OVER (PARTITION BY emp_name ORDER BY emp_id) AS count_no FROM employee

)

DELETE FROM employeeCTE WHERE count_no > 1


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

Posted by: Rajkatie on: 6/25/2012 [Member] [MVP] Gold | Points: 25

Up
0
Down
Kindly refer following link
http://blog.sqlauthority.com/2009/06/23/sql-server-2005-2008-delete-duplicate-rows/

Rajesh Patel
Developer Trainer | Software Craftsman

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

Posted by: CGN007 on: 6/25/2012 [Member] Silver | Points: 25
Posted by: Sriramnandha on: 6/26/2012 [Member] Starter | Points: 25

Up
0
Down
DELETE
FROM TABLENAME
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM TABLENAME
GROUP BY COLUMNNAME)

Hope this will help

Regards
Sriram.R

sriram

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

Posted by: Sriramnandha on: 6/26/2012 [Member] Starter | Points: 25

Up
0
Down
DELETE FROM table_name A WHERE ROWID >

( SELECT min(rowid) FROM table_name B

WHERE A.key_values = B.key_values)

sriram

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

Posted by: Weblorquins on: 7/8/2012 [Member] Starter | Points: 25

Up
0
Down

Delete From TableName
Where ID Not IN
{
Select MAX(ID) From TableName
Group By Col1, Col2...
}

http://sqlserver4us.blogspot.in/2012/07/how-to-delete-duplicate-records-in-sql.html

For more SQL server Interview question and answer click below link
http://sqlserver4us.blogspot.in

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

Login to post response