Deleting duplicates is common practice and there are several neat tricks to easily delete your duplicates in a table. Here are two great solutions depending on your needs.
First, I want to show you a quick and neat syntax that can be used if it doesn't matter what duplicates you delete:
DELETE FROM tablename WHERE DuplicateCount > 1 GO
However, if we want to delete the newest duplicates and keep the original, we require a little more coding. Here is a solution utilizing a CTE:
WITH deleteCTE AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RowNumber
FROM table1
)
Delete FROM deleteCTE WHERE RowNumber > 1
Comentários