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

#mysql

Есть таблица с дубликатами в MySQL:
(по полю name)
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
    

Ответы

Ответ 1

Запрос для результирующей таблицы:
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
);

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


Ответ 2

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

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;


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


Ответ 3

Я бы предложил пересоздать полностью таблицу, установив нужные уникальные столбцы.
И отправив в 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 )


Ответ 4

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

ALTER IGNORE TABLE foobar ADD UNIQUE (name, surname)


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

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


Ответ 5

По идее как то так можно:
delete from table where id in (select max(id) from table group by name having count(id)>1)

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


Ответ 6

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 )


Ответ 7

Еще один способ через конструкцию Insert ... Select

Сначала создадим новую таблицу с ключем unique, чтобы в будущем запретить вставку
дубликатов. 

CREATE TABLE new_table (
  name varchar(255) NOT NULL,
  surname varchar(255) NOT NULL,
  UNIQUE KEY name_surname (name,surname)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


Затем заполним созданную таблицу данными из исходной таблицы

INSERT INTO new_table (new_table.name, new_table.surname) SELECT old_table.name,
old_table.surname
FROM old_table
GROUP BY old_table.name, old_table.surname
HAVING COUNT(old_table.id)<2; --выбираем записи которые НЕ дублируются

INSERT INTO new_table (new_table.name, new_table.surname) SELECT old_table.name,
old_table.surname
FROM old_table
GROUP BY old_table.name, old_table.surname
HAVING COUNT(old_table.id)>1; --выбираем записи которые дублируются


В мое случае в таблице содержалось ~260 тыс. записей, из них 2 тыс. дублировались.


Ответ 8

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 );


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


Ответ 9

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

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
Ответ 10

Я, вероятно, извращенец, так как работал с 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)