SQL Delete duplicate rows
January 1, 2023 2023-01-01 12:24SQL Delete duplicate rows
Subhajit 
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.
