Глиф Учебники

Как удалить дубликаты в SQL

Справочник · SQL · 2 минуты чтения

Дубликаты удаляют в три шага: найти, удалить все копии, кроме одной, и запретить повторы на будущее. Пример — таблица контактов, где один адрес почты записан несколько раз:

SQL
CREATE TABLE contacts (
    id    INTEGER PRIMARY KEY,
    name  TEXT,
    email TEXT
);

INSERT INTO contacts (name, email) VALUES
    ('Анна', 'anna@example.com'),
    ('Борис', 'boris@example.com'),
    ('Анна К.', 'anna@example.com'),
    ('Вера', 'vera@example.com'),
    ('Борис', 'boris@example.com'),
    ('Анна', 'anna@example.com');

Найти дубликаты

SQL
SELECT email, COUNT(*) AS copies
FROM contacts
GROUP BY email
HAVING COUNT(*) > 1;
emailcopies
anna@example.com3
boris@example.com2

Дубликатом считается то, что совпадает в столбцах GROUP BY. Если повтором считается совпадение имени и почты сразу, пишите GROUP BY name, email.

Удалить, оставив одну строку

В каждой группе остаётся строка с наименьшим id (самая ранняя), остальные удаляются.

Удаление необратимо. Перед ним сделайте резервную копию базы (как) или выполняйте команду внутри транзакции: BEGIN;, DELETE, проверка, COMMIT;.

Запрос зависит от СУБД: в MySQL способ с подзапросом не работает.

SQLite и PostgreSQLудаление с подзапросом

Сначала посмотрите, что уйдёт:

SQL
SELECT * FROM contacts
WHERE id NOT IN (SELECT MIN(id) FROM contacts GROUP BY email);
idnameemail
3Анна К.anna@example.com
5Борисboris@example.com
6Аннаanna@example.com

Если список верный, замените SELECT * на DELETE:

SQL
DELETE FROM contacts
WHERE id NOT IN (SELECT MIN(id) FROM contacts GROUP BY email);

Остались строки 1, 2 и 4 — по одной на адрес. Чтобы оставить самую новую строку, а не первую, напишите MAX(id).

Если в таблице нет столбца id, в SQLite его заменяет скрытый номер строки rowid: MIN(rowid) и WHERE rowid NOT IN (…). В PostgreSQL такую роль играет служебный столбец ctid.

Дальше: «Запретить повторы»

MySQLудаление соединением таблицы с собой

MySQL не разрешает подзапросу читать ту таблицу, из которой идёт удаление: ошибка 1093, You can't specify target table 'contacts' for update in FROM clause. Там дубликаты удаляют соединением таблицы с самой собой:

SQL
DELETE c1 FROM contacts c1
JOIN contacts c2 ON c1.email = c2.email AND c1.id > c2.id;

Строка c1 удаляется, если есть строка c2 с тем же адресом и меньшим номером. Остаётся строка с наименьшим id.

Запретить повторы

После очистки поставьте уникальный индекс — тогда новый дубликат просто не запишется:

SQL
CREATE UNIQUE INDEX idx_contacts_email ON contacts (email);

Попытка добавить ещё одну строку с anna@example.com закончится ошибкой UNIQUE constraint failed: contacts.email. Пока дубликаты не удалены, создать такой индекс не получится.

Подводные камни