Форум MySQL

В нормальных базах данных, где вложенные запросы появлись не пару лет назад, а гораздо раньше решить проблему можно на раз, однако в MySQL эта проблема не решается из-за того, что в операторах UPDATE и DELETE по сути невозможно использовать коррелированные запросы.
Пусть имеется таблица

CREATE TABLE cnt (   id int(11) NOT NULL auto_increment,   `name` varchar(55) NOT NULL,   phone varchar(20) NOT NULL,   PRIMARY KEY  (id),   KEY namephone (`name`,phone) ) ENGINE=MyISAM  DEFAULT CHARSET=cp1251; INSERT INTO cnt VALUES(1, 'Александр', '777777'); INSERT INTO cnt VALUES(2, 'Виктор', '555444'); INSERT INTO cnt VALUES(3, 'Евгений', '228833'); INSERT INTO cnt VALUES(4, 'Виктор', '454454'); INSERT INTO cnt VALUES(5, 'Алексей', '223885'); INSERT INTO cnt VALUES(6, 'Михаил', '664488'); INSERT INTO cnt VALUES(7, 'Виктор', '990088'); INSERT INTO cnt VALUES(8, 'Евгений', '228833');

В которой строки с позициями 3 и 8 являются дублирующими, если в каком-нибудь Oracle удалить дублирующе записи можно было бы при помощи запроса вида (ну или не совсем таким, но выкрутиться можно)

DELETE FROM cnt AS fst WHERE fst.id < (SELECT MAX(snd.id) FROM cnt AS snd             WHERE fst.name = snd.name AND                   fst.phone = snd.phone)

то MySQL вернет ошибку

#1093 - You can't specify target table 'cnt' for update in FROM clause

Причем "обмануть" движок не удастся и при помощи SELECT, где вложенные запросы допускаются. Т.е. запрос следующего вида тоже не сработает.

DELETE FROM cnt WHERE id IN (SELECT id FROM cnt AS fst WHERE fst.id < (SELECT MAX(snd.id) FROM cnt AS snd             WHERE fst.name = snd.name AND                   fst.phone = snd.phone))

Остается только получать список при помощи запроса

SELECT id FROM cnt AS fst WHERE fst.id < (SELECT MAX(snd.id) FROM cnt AS snd             WHERE fst.name = snd.name AND                   fst.phone = snd.phone)

И удалять записи либо при помощи курсора, либо при помощи внешнего кода (например, на PHP).

Как стал бы решать такую задачу я.

Определил бы конфликтующие идентификаторы и актуальный первичный ключ

SELECT name, phone, MAX( id ) AS maxid   FROM `cnt` c1   GROUP BY name, phone   HAVING COUNT( * ) >1

as lim


а затем бы построил запрос DELETE по сложному табличному выражению

DELETE cnt    FROM cnt     JOIN  lim       ON (cnt.name, cnt.phone) = (lim.name,lim2.phone) AND c2.id < maxid

Подставив одно в другое, выполнил бы

DELETE c2    FROM cnt c2     JOIN     (SELECT name,phone, MAX(id) AS maxid       FROM `cnt` c1      GROUP BY name, phone      HAVING  COUNT(*) > 1     ) AS lim       ON (c2.name, c2.phone) = (lim.name,lim.phone) AND c2.id < maxid

и получил

Удалено строк: 1 (запрос занял 0.0158 сек.)

Конечно, я не стал бы так делать.

Если пара name,phone по модели уникальна, я бы просто создал на ней уикальный индекс, и такая ситуация просто не возникла бы.

Здорово, будет теперь тема, на которую можно ссылаться.

PS Все ж такая проблема возникает, например, в приведенном выше дампе - это старые телефонные базы данных и вообще давно существующие базы данных (которые изначально возникли в таких СУБД, в которых возможно и понятия уникального столбца не существовало).

Я и не сомневался, собственно, что проблема у Вас не на пустом месте возникла.
Просто для остальных дал понять, что если СУБД и модель позволяет, то безобразиев, когда вынужден искать и удалять дубликаты, лучше стараться избечь заранее.

Кстати, надо полагать, база немаленькая.
Перед тем, как выполнять эти запросы, всё ж стоило бы создать составной индекс (не уникальный, обычный, конечно). А то все эти ON и GROUP BY над полями (name,phone) боком выйдут.

На самом деле такая задача действительно стояла, но достаточно давно (у нас уже и call-центра то нет, для которого она решалась), к ней я вернулся в связи с тем, что решил почитать древние книги по SQL и порадоваться как мы сейчас хорошо живем с современными технологиями :))). Вместо этого натыкаюсь на элегантное удаление дублирующих записей, которое мне никогда в голову не приходило, и которое, ко всему прочему, не работает в MySQL. От дасады собственно и родилась эта тему. Вопрос-то все-равно регулярно возникает и уже не первый год, пусть будет тема, на которую можно будет сослаться при случае, тем более тут и мораль и решение в одном месте :))).

подобные темы надо выделять красным и складировать на видное место.
спасибо.

Кстати, всё-таки движок можно обмануть заставив для внутреннего подзапроса использовать временную таблицу.
Для этого предпоследний запрос запишем в таком виде

DELETE FROM cnt  WHERE id IN (               SELECT id FROM (SELECT id FROM cnt AS fst WHERE fst.id <                  (                   SELECT MAX(snd.id) FROM cnt AS snd WHERE fst.name = snd.name AND fst.phone = snd.phone                 )) as x              )

Ессно не стоит такие фокусы делать на сильно больших объемах данных