How to Delete Duplicate Rows in SQL (MySQL, PostgreSQL, SQL Server)

Introduction

Duplicate rows are one of the most common data quality issues. The trick is deleting the duplicates while keeping exactly one copy of each unique row.

Find Duplicates First

Before deleting, identify which rows are duplicated and how many copies exist.

-- Find duplicates by email:
SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY copies DESC;

Delete with ROW_NUMBER (PostgreSQL & SQL Server)

The cleanest method — assign row numbers to each group and delete all rows except the first.

-- PostgreSQL:
DELETE FROM users
WHERE id IN (
  SELECT id FROM (
    SELECT id,
      ROW_NUMBER() OVER (
        PARTITION BY email
        ORDER BY id ASC
      ) AS rn
    FROM users
  ) ranked
  WHERE rn > 1
);

-- SQL Server (using CTE):
WITH duplicates AS (
  SELECT id,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY id ASC
    ) AS rn
  FROM users
)
DELETE FROM duplicates WHERE rn > 1;

Delete with Self-Join (MySQL)

MySQL doesn't support DELETE with CTE well in older versions. Use a self-join instead.

-- Delete duplicates keeping the lowest id:
DELETE u1
FROM users u1
INNER JOIN users u2
  ON u1.email = u2.email
  AND u1.id > u2.id;

-- MySQL 8.0+ supports the ROW_NUMBER approach too:
DELETE FROM users
WHERE id NOT IN (
  SELECT min_id FROM (
    SELECT MIN(id) AS min_id
    FROM users
    GROUP BY email
  ) AS keepers
);

Delete with Temporary Table (Safe Method)

The safest approach — copy unique rows to a temp table, truncate, and re-insert.

-- 1. Copy unique rows:
CREATE TABLE users_clean AS
SELECT DISTINCT ON (email) *
FROM users
ORDER BY email, id ASC;  -- PostgreSQL

-- For MySQL:
CREATE TABLE users_clean AS
SELECT * FROM users
WHERE id IN (
  SELECT MIN(id) FROM users GROUP BY email
);

-- 2. Swap tables:
ALTER TABLE users RENAME TO users_old;
ALTER TABLE users_clean RENAME TO users;

-- 3. Verify, then drop old:
DROP TABLE users_old;

Prevent Future Duplicates

After cleaning up, add constraints to prevent duplicates from happening again.

-- Add unique constraint:
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

-- Or unique index:
CREATE UNIQUE INDEX idx_users_email ON users (email);

-- For soft-duplicate prevention (case insensitive):
CREATE UNIQUE INDEX idx_users_email_lower
ON users (LOWER(email));  -- PostgreSQL only

Best Practices

• Always backup before deleting duplicates
• Find and review duplicates with SELECT before DELETE
• Use the temporary table method for large tables in production
• Add UNIQUE constraints after cleanup to prevent recurrence
• Consider which copy to keep (oldest, newest, most complete)

Generate SQL Queries with AI2SQL

Skip the syntax lookup. Describe what you need in plain English and AI2SQL generates the correct SQL query instantly.

Frequently Asked Questions