Как удалить копии строк без создания/модификации таблиц и отсутствии primary key?

Question

Есть таблица с локациями где надо оставить 1 уникальную запись. Как удалить копии строк без ключа, модификации или создания таблиц?

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

DELETE base from locations AS base
JOIN (SELECT * FROM locations
GROUP BY country, city
HAVING COUNT(city) > 1
ORDER BY COUNT(city) DESC) AS newT
WHERE newT.country = base.country
AND newT.city = base.city;

Answer 1

DELETE t1
FROM locations t1
JOIN locations t2 USING (country, city)
WHERE t1.id > t2.id

Запрос удалит все дубликаты по (country, city), оставив в каждой группе только одну запись - с наименьшим id.

Ну и сразу создайте уникальный индекс по указанной группе полей - чтобы такие дубликаты не появлялись в будущем.

колонки id или primary key не существует – Pashok

CREATE PROCEDURE sp_remove_duplicates ()
BEGIN
  REPEAT
    WITH cte AS ( 
      SELECT country, city
      FROM locations 
      GROUP BY 1, 2
      HAVING COUNT(*) > 1
      LIMIT 1 
      )
    DELETE 
    FROM locations 
    WHERE (country, city) IN (
      SELECT country, city
      FROM cte
      ) 
    LIMIT 1;
  UNTIL NOT ROW_COUNT() END REPEAT;
END

DEMO

Удаление выполняется по одной записи, так что наберитесь терпения.

Answer 2

К сожалению не знаком со спецификой mysql. В ms sql это можно было сделать двумя способами:

Сделать курсором цикл по выборке из таблицы с сортировкой по country и city. В цикле сохранять значения этих столбцов в дублирующие переменные и на следующей итерации сравнивать значения из текущей строки и прошлой (сохранённые в переменные). Если совпадают, удалять текущую строку курсора.
При помощи ROW_NUMBER пронумеровать все дубликаты, и удалить те, у которых номер больше единицы:

DELETE FROM locations
WHERE ROW_NUMBER() OVER(PARTITION BY country, city ORDER BY country) > 1

Сортировка в ORDER BY нам не важна, но в MS SQL она обязательная

БЛОГ НА HUSL

Как удалить копии строк без создания/модификации таблиц и отсутствии primary key?

Ответы (2 шт):