How to Delete Duplicate Rows in sql
There are many ways to identify and delete duplicate rows in SQL. The best way to achieve this would be by using the ROW_NUMBER over Partition by column list. This will partition the table properly with segregating the duplicate records and the unique records will have the row numbers as 1 so that finally we could use delete statement with row number greater than 1.
CTE Query Syntax to delete duplicate records in SQL
WITH CTE AS(
SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1)
DELETE FROM CTE WHERE RN > 1
Let us look at the below example to delete the duplicate rows in SQL
Query to Delete duplicates rows in SQL
WITH EmployeeTable AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ID, Name, Sal ORDER BY ID, Name, Sal) AS RowNumber FROM Employee
DELETE FROM EmployeeTable WHERE RowNumber > 1;
The output from the above query after executing will look like this.