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)
   FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1

Let us look at the below example to delete the duplicate rows in SQL

Employee Table with Duplicate Records:

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.