How to Remove Duplicate Records in SQL?

Use Disctinct.

<p>
To remove duplicate records, I usually use
<code>
ROW_NUMBER()
</code>
with a CTE. I partition the data by the duplicate column, keep the first record (
<code>
ROW_NUMBER = 1
</code>
), and delete the remaining records. This is a safe and commonly used approach in SQL Server, PostgreSQL, and other modern databases.
</p>

Removing duplicate records in SQL depends on how you define a duplicate and which database you’re using (SQL Server, MySQL, Oracle, PostgreSQL). Below are the most common and practical approaches, explained step-by-step.

1️⃣ Identify Duplicate Records (Best Practice – First Step)

Example Table

Employee(Id, Name, Email)

Find duplicates based on Email

SELECT Email, COUNT(*) AS Total
FROM Employee
GROUP BY Email
HAVING COUNT(*) > 1;

2️⃣ Remove Duplicates Using ROW_NUMBER() (Recommended)

✅ Works in SQL Server, MySQL 8+, PostgreSQL, Oracle

Keep the latest record (highest Id)

WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY Email ORDER BY Id DESC) AS rn
    FROM Employee
)
DELETE FROM CTE
WHERE rn > 1;

✔ Keeps one record per Email
✔ Deletes all extra duplicates

3️⃣ Remove Duplicates Without CTE (MySQL < 8)

DELETE e1
FROM Employee e1
JOIN Employee e2
ON e1.Email = e2.Email
AND e1.Id > e2.Id;

✔ Keeps record with lowest Id

4️⃣ Remove Duplicates Using DISTINCT (Create Clean Table)

⚠ Use when table has no primary key

CREATE TABLE Employee_New ASSELECT DISTINCT *FROM Employee;

DROP TABLE Employee;

ALTER TABLE Employee_New RENAME TO Employee;

5️⃣ Remove Duplicates Using GROUP BY

DELETE FROM Employee
WHERE Id NOT IN (
    SELECT MIN(Id)
    FROM Employee
    GROUP BY Email
);

✔ Keeps one row per Email
❌ Slower for large tables

6️⃣ Prevent Future Duplicates (Very Important 🔐)

Add UNIQUE Constraint

ALTER TABLE Employee
ADD CONSTRAINT UQ_Employee_Email UNIQUE (Email);

Now duplicates can’t be inserted again 🚀

7️⃣ SQL Server Specific (Using Temp Table)

SELECT *INTO #TempEmployee
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY Email ORDER BY Id) rn
    FROM Employee
) t
WHERE rn = 1;

TRUNCATE TABLE Employee;

INSERT INTO Employee
SELECT * FROM #TempEmployee;

🧠 Which Method Should You Use?

To remove duplicate records in SQL, you can use a query with functions like ROW_NUMBER() to identify duplicate rows based on specific columns, then delete the extra records while keeping one unique entry. This is commonly done using a Common Table Expression (CTE) that assigns a row number to each record within a group of duplicates, allowing you to safely remove the unwanted rows without losing valid data.