Поиск и удаление неуникальных строк в таблице, SQL only

Для того чтобы найти все неуникальные значения (либо комбинации значний, например из нескольких столбцов) понадобится группировка данных и оператор HAVING с агрегатной функцией COUNT. Ничего сложного :)

SELECT time_begin FROM booking GROUP BY time_begin HAVING COUNT(*) > 1;

Больше всего интересует: GROUP BY time_begin HAVING COUNT(*) > 1

Дословно можно прочитать как: группировать значения по столбцу time_begin где размер группы более 1 записи.

В итоге будут получены все неуникальные значения стоблца time_begin.

В моей задаче требовалось удалить все неуникальные значения (полсностью все). И не долго думая можно попробовать удалить так:

DELETE FROM booking WHERE time_begin IN(SELECT time_begin FROM booking GROUP BY time_begin HAVING COUNT(*) > 1)

И конечно же так не получится, потому что нельзя вносить изменения в таблицу, из которой делается выборка в одном запросе. Но можно создать временную таблицу, занести в нее идентифицирующие значения (в данном случае time_begin) и используя эту временную таблицу удалить данные из целевой таблицы:

START TRANSACTION;

# создание временной таблицы с одним столбцом
CREATE TEMPORARY TABLE temp (
  time_begin INT
);

# вставка значений во временную таблицу из результатов выборки
INSERT INTO temp
SELECT time_begin FROM booking GROUP BY time_begin HAVING COUNT(*) > 1;

# удаление строк из целевой таблицы на основании выборки из временной таблицы
DELETE FROM booking WHERE time_begin IN (SELECT time_begin FROM temp);

# удаление временной таблицы
DROP TEMPORARY TABLE temp;

COMMIT;

В другой задаче потребовалось найти и удалить все неуникальные комбинации значений из нескольких столбцов:

START TRANSACTION;

CREATE TEMPORARY TABLE temp (
  value INT,
  status INT,
  c INT
);

INSERT INTO temp
SELECT `value`, `status`, COUNT(*) FROM `test1` GROUP BY `value`, `status` HAVING COUNT(*) > 1;

DELETE test1 FROM test1, temp WHERE test1.value = temp.value AND test1.status = temp.status;

DROP TEMPORARY TABLE temp;

COMMIT;

Однако код выше удалит и дубликаты и оригиналы, но задача требовала оставить оригиналы ... чтобы оставить оригиналы (те что имеют меньший id) надо заюзать следующий код:

START TRANSACTION;

CREATE TEMPORARY TABLE temp (
  `id` INT,
  `value` INT,
  `status` INT,
  `c` INT
);

INSERT INTO temp
SELECT MIN(`id`) as `id`, `value`, `status`, COUNT(*) FROM `test1` GROUP BY `value`, `status` HAVING COUNT(*) > 1;

DELETE `test1` FROM `test1`, `temp` WHERE `test1`.`value` = `temp`.`value` AND `test1`.`status` = `temp`.`status` AND `test1`.`id` != `temp`.`id`;

DROP TEMPORARY TABLE `temp`;

COMMIT;

Теперь в таблице останутся строки только с меньшим id. Для того чтобы оставить самые последние дубликаты, а все остальные дубликаты удалить вместо MIN(id) надо использовать MAX(id).