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:
Delete Duplicate Records 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.

Delete Duplicate Rows in sql

0 Shares
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like
XOR in Python

XOR in Python

XOR Operator in Python is also known as “exclusive or”  that compares two binary numbers bitwise if two bits are…
How To

How to configure Git proxy?

If you are working in the corporate company all the internet requests usually goes through the corporate firewall.…