Удалить дубликаты с условием

У меня есть таблица contacts, которая содержит повторяющиеся записи:

id name is_contacted created_at

Мне нужно удалить дубликаты, но оставить первую запись (среди дубликатов для каждого имени), где is_contacted=1. Если среди дубликатов записей нет записей, где is_contacted=1, просто оставьте первую.

Это то, что у меня есть до сих пор:

DELETE c1 FROM contacts c1
INNER JOIN contacts c2 
WHERE
    c1.id > c2.id AND 
    c1.name = c2.name;

🤔 А знаете ли вы, что...
MySQL может быть использован как веб-серверами, так и при разработке автономных приложений.


52
2

Ответы:

Ниже запрос будет фильтровать только те записи, которые вы хотите. Вы не упомянули, что такое первичный ключ в вашей таблице, поэтому я не знаю, как соединить это обратно 1: 1 со всей вашей таблицей.

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

SELECT * FROM
(
SELECT *,
ROW_NUMBER(PARTITION BY name ORDER BY CASE WHEN is_contacted = 1 THEN -999999 else is_contacted END ) AS RN_
from contacts
) c
WHERE c.RN_ = 1

Решено

Предполагая, что тип данных is_contacted равен BOOLEAN, а id является первичным ключом таблицы, и это столбец, который определяет порядок и какую строку следует рассматривать первой, используйте оконную функцию ROW_NUMBER для ранжирования строк каждого name:

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY is_contacted DESC, id) rn
  FROM contacts
)
DELETE t
FROM contacts t INNER JOIN cte c
ON c.id = t.id
WHERE c.rn > 1;

ORDER BY is_contacted DESC, id возвращает строки с is_contacted = 1 вверху (если они существуют).

Для версий MySql до 8.0 без поддержки CTE и функций окна используйте соединение таблицы с запросом, который использует агрегацию, чтобы получить id строки, которую вы хотите сохранить:

DELETE t
FROM contacts t 
INNER JOIN ( 
  SELECT name,
         COALESCE(MIN(CASE WHEN is_contacted THEN id END), MIN(id)) id         
  FROM contacts
  GROUP BY name
) c ON c.name = t.name AND c.id <> t.id;