Удалить все, кроме одного, дубликата записи

У меня есть таблица, которая должна содержать следы посетителей для данного профиля (идентификатор пользователя для пары идентификаторов пользователя). Оказывается, мой SQL-запрос был немного выключен и создает несколько пар вместо одиночных, как и предполагалось. С задним числом я должен был принудительно установить уникальное ограничение для каждой пары id + id.

Теперь, как я могу почистить столик? Я хочу удалить все повторяющиеся пары и оставить только один.

Так, например, измените это:

23515 -> 52525 date_visited
23515 -> 52525 date_visited
23515 -> 52525 date_visited
12345 -> 54321 date_visited
12345 -> 54321 date_visited
12345 -> 54321 date_visited
12345 -> 54321 date_visited
23515 -> 52525 date_visited
...

В это:

23515 -> 52525 date_visited
12345 -> 54321 date_visited

Обновление. Вот как выглядит структура таблицы:

id  int(10)         UNSIGNED    Non     Aucun   AUTO_INCREMENT
profile_id  int(10)         UNSIGNED    Non     0 
visitor_id  int(10)         UNSIGNED    Non     0
date_visited    timestamp           Non     CURRENT_TIMESTAMP

Ответ 1

Использовать группу в подзапросе:

delete from my_tab where id not in 
(select min(id) from my_tab group by profile_id, visitor_id);

Вам нужен какой-то уникальный идентификатор (здесь я использую id).

ОБНОВЛЕНИЕ

Как указано @JamesPoulson, это вызывает синтаксическую ошибку в MySQL; правильное решение (как показано в ответе Джеймса):

delete from `my_tab` where id not in
( SELECT * FROM 
    (select min(id) from `my_tab` group by profile_id, visitor_id) AS temp_tab
);

Ответ 2

Здесь решение Фрэнка Шмитта с небольшим временным решением для временной таблицы:

delete from `my_tab` where id not in
( SELECT * FROM 
    (select min(id) from `my_tab` group by profile_id, visitor_id) AS temp_tab
)

Ответ 3

Выбрать все уникальные строки
Скопируйте их в новую временную таблицу
Усекать исходный стол
Скопируйте данные таблицы temp в исходную таблицу

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

Ответ 4

Это будет работать:

With NewCTE
AS
(
Select *, Row_number() over(partition by ID order by ID)as RowNumber from 
table_name
)
Delete from NewCTE where RowNumber > 1