Delete Duplicate Rows from Table

Posted by Rk123456 under Sql Server on 4/11/2010 | Views : 1872 | Status : [Member] | Replies : 6
1.When table has Identity Column

Delete from TBLABC
where ID not in (Select Max(ID) from TBLABC group by A,B,C )

2. With Common Table Expression by Using ROW_NUMBER()
with[TBLXYZ] AS
(
Select Row_Number() over(PARTITION BY a,b,c order by a ) as RowID,* from TBLABC
)

Delete from TBLXYZ where RowID > 1

Note-The RowID column returns value greeter than 1 for duplicate Rows.




Responses

Posted by: Vuyiswamb on: 4/12/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Good Day Rk123456

In the Forum we only Post Questions. If you want to share your knowledge you can either write an Article or Share a code here

http://www.dotnetfunda.com/post/?ReturnUrl=/post/PostArticle.aspx

http://www.dotnetfunda.com/post/?ReturnUrl=/post/PostCode.aspx

I will delete this post, please post on that Section.

Thank you for posting at Dotnetfunda

Vuyiswa Maseko

Thank you for posting at Dotnetfunda
[Administrator]

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

Posted by: SurajRane on: 4/12/2010 [Member] Starter

Up
0
Down
Try this.


WITH CTE AS
(
SELECT ROW_NUMBER() OVER
(PARTITION BY [FirstName], [LastName], [Address]
Order BY [FirstName] DESC, [LastName] DESC, [Address] DESC )
AS RowNumber,
[FirstName], [LastName], [Address]
FROM Employee tbl )
DELETE FROM CTE Where RowNumber > 1
GO
SELECT * FROM Employee
GO


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

Posted by: Vuyiswamb on: 4/12/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Good Day SurajRane


Rk123456 does not have a problem nor his asking a Question, i think he is sharing his knowledge.




Thank you for posting at Dotnetfunda
[Administrator]

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

Posted by: Elizas on: 4/28/2010 [Member] Starter

Up
0
Down
The query will be composed this way:-


WITH TempUsers (FirstName,LastName, duplicateRecordCount)
AS
(
SELECT FirstName,LastName,
ROW_NUMBER()OVER(PARTITIONBY FirstName, LastName ORDERBY FirstName) AS duplicateRecordCount
FROM dbo.Users
)
DELETE
FROM TempUsers
WHERE duplicateRecordCount > 1
GO

http://www.mindfiresolutions.com/Deleting-Duplicate-records-in-SQL-Server-522.php

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

Posted by: Markhugy on: 12/2/2010 [Member] Starter | Points: 25

Up
0
Down
Here is a good tool to find and delete duplicate files
www.duplicateFilesDeleter.com

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

Posted by: T.saravanan on: 12/2/2010 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Markhugy,

Actually its not a correct answer what the poster ask means delete the duplicate records in table.But your answer is to delete the duplicate files.
Any how its also useful to one.But not to the poster.


Thanks,
T.Saravanan

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

Login to post response