Delete Duplicate Value from SQL Data Table

In this article, let’s see how to delete a duplicate record which does not have primary key.

For example, in the below table Total, we could find duplicate values occurring. The task is how to delete the duplicates  and retain the unique value.

 Below is the query to accomplish the task.

WITH CTE (tempName, DuplicateCount)

AS

(

SELECT tempName,

ROW_NUMBER() OVER(PARTITION BY tempName ORDER BY tempName) AS DuplicateCount

FROM tempTable

)

DELETE

FROM CTE

WHERE DuplicateCount > 1 and tempName !='' and tempName !=null

GO

Initially just create a temp result using CTE having new columns which uses ROW_NUMBER using partitions and delete the duplicates.

And thus the output will be