Delete duplicate rows but keep one

Clean up a table after finding the duplicates, keeping the oldest of each.

SNP 077 #396 Filed sql mariadb

In MySQL and MariaDB, a self join does it:

sql
DELETE u1
FROM users u1
JOIN users u2
  ON u2.email = u1.email
 AND u2.id < u1.id;

Every row that has an older twin (a smaller id with the same email) is deleted, so the oldest one survives.

In PostgreSQL:

sql
DELETE FROM users
WHERE id NOT IN (
  SELECT MIN(id) FROM users GROUP BY email
);

Run the matching SELECT first and take a backup. Then add a unique index so it can't happen again:

sql
ALTER TABLE users ADD UNIQUE INDEX uniq_email (email);