Find and Delete Duplicate Rows in SQL (Without Deleting Originals)
How to identify duplicates with GROUP BY HAVING, and safely delete them using ROW_NUMBER() or a CTE. Works in MySQL, PostgreSQL, and SQL Server.
Step 1: Find the Duplicates
SELECT email, COUNT(*) as cnt FROM users GROUP BY email HAVING COUNT(*) > 1;
Step 2: See the Full Duplicate Rows
SELECT * FROM users WHERE email IN (SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1) ORDER BY email, id;
Step 3: Delete Duplicates (Keep the Oldest)
PostgreSQL / SQL Server (using CTE): WITH ranked AS (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM users) DELETE FROM users WHERE id IN (SELECT id FROM ranked WHERE rn > 1);
MySQL (no DELETE from CTE): DELETE u1 FROM users u1 INNER JOIN users u2 ON u1.email = u2.email AND u1.id > u2.id;
Prevention: Unique Constraints
The best fix is preventing duplicates at the schema level: ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);