Search

Thursday, November 24, 2011

Delete Duplicate Rows

Here I have used CTE and ROW_NUMBER to delete duplicate records.

/* Delete Duplicate Records */
WITH CTE (Col1, DuplicateRowCount)
AS
(SELECT Col1, ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col1) AS DuplicateRowCount
FROM DuplicateRcordTable)

DELETE FROM CTE WHERE DuplicateRowCount > 1 The above command will delete duplicate record in table i.e DuplicateRcordTable. which have duplicate record of column Col1 in table.




No comments:

Post a Comment