Blog

SQL Delete duplicate rows

SQL

SQL Delete duplicate rows

In the above query we can see that there is more than 1 duplicate columns for Aman Gupta. Now we will use the below CTE (common table expression to see the duplicate count of each rows.)

				
					WITH CTE
AS (SELECT FirstName, 
           LastName,
           ROW_NUMBER() OVER(PARTITION BY FirstName, 
                                          LastName
           ORDER BY EmployeeId) AS DuplicateCount
    FROM [dbo].[EMPLOYEE])
SELECT *
FROM CTE;
				
			

In the above table we can see the duplicatecount of each rows in a table. Like for Aman Gupta 1, 2, 3 and 4. Now If we run the above CTE expression to delete the dupliccatecounts greater than 1, we will be able to remove the duplicate rows from the table.

				
					WITH CTE
AS (SELECT FirstName, 
           LastName,
           ROW_NUMBER() OVER(PARTITION BY FirstName, 
                                          LastName
           ORDER BY EmployeeId) AS DuplicateCount
    FROM [dbo].[EMPLOYEE])
	DELETE FROM CTE
	WHERE DuplicateCount > 1;
				
			

After running the CTE with delete expression., we will see that all the duplicate rows has been removed and there is only one row for Aman Gupta.

Please comment and share if you like this post and tell us about how we can enhance our posts. Thanks.

Subhajit