Tras registros duplicados en una Base de Datos MySQL

Algunas veces nos encontramos con el siguiente caso:

"En el Diseño de la Base de Datos me he saltado un restricción para evitar la inserción de elementos duplicados en una tabla".

O simplemente las inserciones las estamos haciendo de manera automática, levantando la información desde algún archivo o dispositivo y, por "circunstancias", se cae en un bucle de inserción de los mismos datos (vaya que si me ha pasado), entonces nos toca eliminar esos registros duplicados.

Para comprender bien la operativa a realizar vamos a hacer un ejemplo muy sencillo, creamos una tabla del abecedario donde no deben existir letras repetidas y cada letra tendrá su PK numérica.

CREATE TABLE abecedario(     id in] NOT NULL Primary Key,     letra char(1) NOT NULL);

Insertamos 10 filas de ejemplo

id          letra ----------- ----- 1           A 2           B 3           C 4           D 5           A 6           E 7           D 8           F 9           F 10          Z (10 filas afectadas)

Para conocer cuales son las letras que estan repetidas podemos ejecutar este query, que selecciona las letras y las cuenta, agrupa las letras permitiendo que muestre una sola vez la letra repetida y el al lado muestre la cantidad de repeticiones

select letra, COUNT(*) from abecedario group by letra having COUNT(*) > 1;

Pero esto no basta, solo nos muestra los valores repetidos. Si quisiéramos eliminar todos funcionaria en un DELETE, pero el objetivo es eliminar todos los valores duplicados pero dejar uno. Para ello obligatoriamente necesitamos que exista una columna que tenga un identificador único (por ejemplo una PK u otra llave única).

Primero visualizamos cuales serán las filas borradas:

select a1.id ,a1.letra from abecedario as a1 inner join abecedario as a2 on a1.letra = a2.letra and a1.id > a2.id;

Lo que hizo la consulta fue crear dos conjuntos de datos de la misma tabla, y unirlas en las columnas que deberían ser únicas y filtrarlas por que no son iguales al primer valor repetido. Para eliminar las filas ejecutaríamos lo siguiente:

delete from abecedario where id in ( select a3.id from (      select a1.id from abecedario as a1      inner join abecedario as a2      on a1.letra = a2.letra      and a1.id > a2.id ) as a3 );

Quedando así la tabla:

id          letra ----------- ----- 1           A 2           B 3           C 4           D 6           E 8           F 10          Z (7 filas afectadas)