SQL - как удалить дубликаты записей в БД?

#mysql #sql

Есть такая табличка статусов слонов, то есть у слона может быть несколько статусов
его местонахождения.

status:
id int auto_increment pk
slon_id int
status varchar
date datetime


При программном баге (точнее недоработке) появились дубликаты записей вида

1 1 Розовый слон в Африке 18.11.2016...
2 1 Розовый слон в Африке 18.11.2016...
3 1 розовый слон уже в Зимбабве 19.11.2016...
4 2 Зеленый слон в Европе 15.11.2016...


Интересует SQL решение. Можно ли как-то удалить дубликаты записей? Можно ли удалить
дубликаты записей оставив по одному чтобы не потерять историю? (То есть если про Африку
и Розового слона несколько записей - удалить все кроме одной).
    

Ответы

Ответ 1

Алгоритм действий:


понять, по каким критериям надо считать записи идентичными
написать запрос на поиск дублирующихся наборов данных (можно не строк, только наборов
данных)
понять, как решить, какую из записей надо оставить
соответственно откорректировать запрос, чтобы он выводил все дублирующиеся записи,
кроме той, которую надо оставить
написать запрос delete where id in (subquery).


В итоге получается что-то вроде такого, если считать uniqfield полем, которое должно
стать уникальным, оставлять строку с минимальным id:

delete from tablename where id in (
    select id from (
        select id from tablename join (
             select min(id) as firstdup, uniqfield
             from tablename
             group by uniqfield
        ) duplicates using(uniqfield)
        where id != firstdup
    ) subqueryhack
)


Для комбинации из двух полей, чтобы было видно, что меняется

delete from tablename where id in (
    select id from (
        select id from tablename join (
             select min(id) as firstdup, uniqfield1, uniqfield2
             from tablename
             group by uniqfield1, uniqfield2
        ) duplicates using(uniqfield1, uniqfield2)
        where id != firstdup
    ) subqueryhack
)


delete  в mysql не даст напрямую удалять из той таблицы, которую читает подзапрос,
но это обходится при необходимости ещё одним подзапросом.

Если неважно, какие именно строки оставлять, а какие удалять и позволяет версия СУБД
- то до mysql 5.7.4 можно было просто повесить уникальный индекс с указанием ignore

ALTER IGNORE TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D);


Что удалит все дубли по этим полям, кроме какой-то одной строки. В актуальных версиях
ignore поведение удалено и вызывает ошибку. Не вникал, почему удалили, скорей всего
из-за неочевидного поведения, какая именно строка будет удалена и общего курса возвращения
к SQL-стандарту.


Ответ 2

Должен работать этот вариант. TESTTEST нужно заменить на своё имя таблицы.
Данный запрос будет оставлять одну запись для каждого slon_id с максимальной датой.

DELETE FROM TESTTEST WHERE id NOT IN (
    SELECT MAX(id)
    FROM 
    (SELECT t.slon_id, MAX(t.[date]) AS [date] 
    FROM TESTTEST t GROUP BY t.slon_id) t
    JOIN TESTTEST t2 
      ON t.slon_id = t2.slon_id AND t.[date] = t2.[date] 
    GROUP BY t2.id
    )