Запрос SQL на удаление дубликатов из таблицы по одному полю

id |  name | surname
--------------------
1  | niko  | surname
2  | niko  | surname
3  | jane  | surname
4  | jane  | surname
5  | ivan  | surname

id |  name | surname
--------------------
1  | niko  | surname
3  | jane  | surname
5  | ivan  | surname

Запрос для результирующей таблицы:

SELECT min(id), name, surname
FROM `table`
GROUP BY name, surname

Для удаления дубликатов подойдет такой прием:

CREATE TEMPORARY TABLE `t_temp` 
as  (
   SELECT min(id) as id
   FROM `table`
   GROUP BY name, surname
);
DELETE from `table`
WHERE `table`.id not in (
   SELECT id FROM t_temp
);

Есть конечно нюансы...

Я бы предложил пересоздать полностью таблицу, установив нужные уникальные столбцы. И отправив в ignore те данные, которые будут дублироваться. Это будет гораздо быстрее, если у вас в таблице очень много данных.

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

SELECT * 
FROM   tablename 
       INNER JOIN (SELECT Min(id) minid, 
                          name, 
                          surname 
                   FROM   tablename 
                   GROUP  BY name, 
                             surname 
                   HAVING Count(1) > 1) AS duplicatesTable 
               ON ( duplicatesTable.name = tablename.name 
                    AND duplicatesTable.surname = tablename.surname 
                    AND duplicatesTable.minid <> tablename.id )

здесь предлагают делать так:

ALTER IGNORE TABLE foobar ADD UNIQUE (name, surname)

но индекс должен влезть в память.

ну и через временную таблицу естественно есть способ. а так-же через group by.

Какие-то экзотические варианты предлагаются.

http://sqlinfo.ru/articles/info/19.html

Удалить из таблицы дубликаты (строки с одинаковыми значениями поля col) с меньшим id

DELETE t1 FROM t t1 LEFT JOIN t t2 ON t1.col = t2.col AND t1.id < t2.id WHERE t2.id IS NOT NULL;

через подзапрос

DELETE t FROM t LEFT JOIN (SELECT max(id) as id, col FROM t GROUP BY col) t1 USING(id) WHERE t1.id IS NULL;

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

По идее как то так можно:

delete from table where id in (select max(id) from table group by name having count(id)>1)

А потом сразу создать уникальный индекс по полю чтобы потом этого сделать было не возможно.

DELETE
FROM form
WHERE id NOT IN
    (SELECT id
     FROM
       (SELECT max(id) AS id -- какой из дублей нужно оставить
        FROM form
        GROUP BY description_complaints,
                 id_stat,
                 number_phone
        HAVING count(id) > 1
        UNION SELECT id AS id -- не дубли
        FROM form
        GROUP BY description_complaints,
                 id_stat,
                 number_phone
        HAVING count(id) = 1 ) AS dubl )

delete from t1 a where id in
(select max(id) from t1  where name in
(select name from t1  
group by name 
having count(name )>1) and name =a.name );

Думаю, так проще всего будет.

Первый вариант

DELETE FROM table
WHERE id NOT IN (SELECT max(id) FROM table GROUP BY name HAVING count(id)>1)

Второй вариант с помощью Exists

DELETE FROM table t1
WHERE EXISTS (SELECT 1 FROM table t2 WHERE t2.id<t1.id and t1.name=t2.name)

Я, вероятно, извращенец, так как работал с MS Access) решал задачку на вакансию junior sql programmer на авито.. Может, кому пригодится мое решение аналогичной проблемы:

 delete * from cities
 where city_id not in
 (select min(city_id) from cities group by city_name having count(*) > 1) 
 and city_id not in  
 (select min(city_id) from cities group by  city_name  having count(*) = 1)