Удаление дубликатов строк SQL

Question

[Роман Сохарев]

Хочу удалить дубликаты строк.
Различается только поле id (автоинкремент).
Пробовал составить несколько разных "NOT IN" и "NOT EXIST" конструкций, но все они выдают ошибку синтаксиса. Видимо хреновый я составитель запросов. Какими запросами для удаления дубликатов вы пользуетесь?

Comments

[Роман Сохарев]

Хотя решение уже найдено, вы можете оставлять свои варианты - я думаю они будут полезны другим людям, которые будут искать ответ на похожий вопрос.

Answer 1

[Rsa97]

DELETE `t1`.* FROM `table` AS `t1`
    LEFT JOIN (SELECT `id` FROM `table` GROUP BY `field1`, `field2`, ...) AS `t2` 
        ON `t1`.`id` = `t2`.`id`
    WHERE `t2`.`id` IS NULL

Comments

[Роман Сохарев]

Отлично, все сразу отработало. Спасибо.

[AleDv]

Прошу прошения, не очень силён в SQL, не могли бы Вы расписать Ваш запрос, что за что отвечает?

[Rsa97]

AleDv: SELECT группирует все строки таблицы по полям, так что для каждой группы одинаковых строк будет выдан id только какой-то одной из этих строк.
LEFT JOIN дописывает id из выборки к строкам таблицы, помечая таким образом уникальные строки
DELETE удаляет строки с id, не попавшими в выборку, то есть все непомеченные дубликаты строк.
Пример:

id    name    age
 1    Вася      17
 2    Петя      21
 3    Вася      17
 4    Миша    19
 5    Миша    19

Результат SELECT:

id
1
2
4

Результат JOIN:

t1.id    t1.name    t1.age    t2.id
   1         Вася         17          1
   2         Петя         21          2
   3         Вася         17        NULL
   4         Миша       19          4
   5         Миша       19        NULL

Окончательно после удаления:

id    name    age
 1    Вася      17
 2    Петя      21
 4    Миша    19

Могли удалиться не 3 и 5, а например 1 и 5 или 1 и 4 строки, зависит от результата выполнения GROUP BY.

[AleDv]

Благодарю Вас за подробное объяснение и примеры!

Answer 2

[Алексей Скахин]

DELETE FROM tbl WHERE id NOT IN(
  SELECT MAX(id) FROM tbl GROUP BY <поле 1>[,<поле 2>]
)

Comments

[Роман Сохарев]

Как я уже говорил, хреновый я составитель запросов. Но мне кажется, что в этом случае будут удалены не только дубликаты, но и поля не имеющие дубликатов.

[Алексей Скахин]

Нет, все будет пучком.
Внутренний запрос отберет все ID записей:
* Для одной - это текущая запись
* Для нескольких - максимальная
А потом удалит все остальные ID, которые не подходят под этот набор

[Алексей Скахин]

Можно сделать как написал @SashaSkot , но запрос надо будет выполнять несколько раз, если дубликатов больше одного.

[Роман Сохарев]

Окей, теперь он SQL говорит, что нельзя использовать таблицу и в DELETE и в SELECT одновременно. Нужно использовать псевдонимы?

[Александр]

@pihel почему несколько раз? вы внутренний подзапрос выполните отдельно, он вам вернет все ID у которых число дубликов, более 1. 1 раз надо выполнить запрос.

[Алексей Скахин]

Честно сказать - не знаю. Думал тут проблем не будет.
Попробуйте создать псевдонимы.
Может у вас 4 версий mysql? Вроде в ней нельзя удалять записи используя подзапросы bugs.mysql.com/bug.php?id=5037
Если так, то получается надо создавать временную таблицу для хранения ID или делать удаление на стороне серверного кода.

[Роман Сохарев]

@SashaSkot , видимо @pihel не обратил внимания на то, что используется "IN" а не "NOT IN"... в любом случае, я воспользовался решением от @Rsa97.

Answer 3

[Александр]

delete from table where id IN (
select id from (
select field, Count(field) as Count
from table
group by field
having count(field)>1) )