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.

 

tempName

GAURI ANSHU

GAURI ANSHU

ISHAN

ISHAN

RISHIKA MUSKAN

SHASWITA

SIDDHARTH SINGH

SIDDHARTH SINGH

SHWET RAJ

 

 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

 

tempName

GAURI ANSHU

ISHAN

RISHIKA MUSKAN

SHASWITA

SIDDHARTH SINGH

SHWET RAJ

 

 

 

 

 

 

 

 

 

 

No Comments