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 |