SQL
Identify and Delete Duplicate Rows Efficiently
Discover SQL techniques to find duplicate records in your table using window functions and Common Table Expressions, and safely remove extra duplicates while preserving one unique entry.
WITH Duplicates AS (
SELECT id, email_address, full_name,
ROW_NUMBER() OVER (PARTITION BY email_address, full_name ORDER BY id) as rn
FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM Duplicates WHERE rn > 1);
How it works: This query uses a Common Table Expression (CTE) named `Duplicates` to identify redundant rows. `ROW_NUMBER() OVER (PARTITION BY email_address, full_name ORDER BY id)` assigns a sequential number to each row within groups that have identical `email_address` and `full_name`. Any row with `rn > 1` is considered a duplicate. The final `DELETE` statement then removes all identified duplicates, preserving the row with the lowest `id` within each duplicate group.