sql - solo - Eliminar todo menos un registro duplicado

sql eliminar registros repetidos dejando solo 1

Question

Tengo una tabla que se supone que debe mantener un seguimiento de los visitantes a un perfil determinado (ID de usuario a par de ID de usuario). Resulta que mi consulta SQL estaba un poco apagada y está produciendo varios pares en lugar de uno solo como estaba previsto. En retrospectiva, debería haber aplicado una restricción única en cada par de id + id.

Ahora, ¿cómo podría ir sobre la limpieza de la mesa? Lo que quiero hacer es eliminar todos los pares duplicados y dejar solo uno.

Así que por ejemplo cambia esto:

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
...

Dentro de esto:

23515 -> 52525 date_visited
12345 -> 54321 date_visited

Actualización: Aquí está la estructura de la tabla según lo solicitado:

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   

Answer 1

Aquí está la solución de Frank Schmitt con una pequeña solución para la tabla temporal:

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
)

Answer 2

Esto funcionará:

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

Answer 3

Seleccionar todas las filas únicas
Cópialos en una nueva tabla temporal.
Mesa original truncada
Copie los datos de la tabla temporal a la tabla original

Eso es lo que yo haría. No estoy seguro si hay 1 consulta que haría todo esto por ti.

Answer 4

Use group by en una subconsulta:

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

Necesitas algún tipo de identificador único (aquí, estoy usando id).

ACTUALIZAR

Como lo señaló @JamesPoulson, esto causa un error de sintaxis en MySQL; La solución correcta es (como se muestra en la respuesta de James ):

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