SQL DELETE DUPLICATE ROWS

Rishabh saini March 13, 2025 2 min read

SQL DELETE DUPLICATE ROWS

When dealing with large datasets, duplicate records are common and can cause inaccurate results. This guide shows you how to both identify duplicates with DISTINCT and permanently remove them from a table.

Complete Python Course with Advance topics:-
SQL Tutorial:-

Step 1: Find Unique Records with DISTINCT

The DISTINCT keyword retrieves unique values from a column or combination of columns:

SELECT DISTINCT column1, column2
FROM table_name
WHERE [conditions];

-- Example: unique salaries
SELECT DISTINCT SALARY FROM EMPLOYEE ORDER BY SALARY;

Step 2: Delete Duplicates Using ROW_NUMBER() (CTE)

The most reliable way to delete duplicates while keeping one copy uses a CTE with ROW_NUMBER() (works in SQL Server, PostgreSQL, MySQL 8+):

WITH cte AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY NAME, SALARY, CITY
      ORDER BY EMP_ID
    ) AS rn
  FROM EMPLOYEE
)
DELETE FROM cte WHERE rn > 1;

This keeps the first occurrence (rn = 1) of each duplicate group and deletes the rest.

Delete Duplicates Using Self-Join (MySQL)

DELETE e1 FROM EMPLOYEE e1
INNER JOIN EMPLOYEE e2
WHERE e1.EMP_ID > e2.EMP_ID
  AND e1.SALARY = e2.SALARY
  AND e1.NAME = e2.NAME;

Download New Real Time Projects:- Click here
Complete Advance AI topics:-

Conclusion

Handling duplicate records preserves data integrity. Use DISTINCT to view unique rows, and a ROW_NUMBER() CTE or self-join to permanently delete duplicates while keeping one copy. Always back up before deleting. For more SQL tutorials, stay tuned to .

sql delete duplicate rows keep one
delete duplicate rows in sql w3schools
sql delete duplicate rows based on one column
delete duplicate records in sql server using row_number
sql delete duplicate rows cte
delete duplicate rows in oracle
remove duplicate rows sql
sql distinct example

SQL DELETE DUPLICATE ROWS