Delete Duplicate Rows from Table

Posted by Rk123456 under Sql Server on 4/11/2010 | Views : 3453 | Status : [Member] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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.

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

0	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

0	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

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

0	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

0	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

Latest Posts