eliminar - ¿Cómo elimino todos los registros duplicados en una tabla MySQL sin tablas temporales?

eliminar registros de 2 tablas mysql

Question

He visto una serie de variaciones sobre esto, pero nada coincide con lo que estoy tratando de lograr.

Tengo una tabla, TableA , que contiene las respuestas dadas por los usuarios a cuestionarios configurables. Las columnas son member_id, quiz_num, question_num, answer_num .

De alguna manera, algunos miembros obtuvieron sus respuestas enviadas dos veces. Así que necesito eliminar los registros duplicados, pero asegúrese de que quede una fila.

No hay una columna primaria , por lo que podría haber dos o tres filas, todas con los mismos datos exactos.

¿Hay una consulta para eliminar todos los duplicados?

Answer 1

Agregue Índice Único en su mesa:

ALTER IGNORE TABLE TableA   
ADD UNIQUE INDEX (member_id, quiz_num, question_num, answer_num);

funciona muy bien

Answer 2

Agregue Índice Único en su mesa:

ALTER IGNORE TABLE `TableA`   
ADD UNIQUE INDEX (`member_id`, `quiz_num`, `question_num`, `answer_num`);

Otra forma de hacer esto sería:

Agregue la clave principal en su tabla y luego puede eliminar fácilmente los duplicados de su tabla con la siguiente consulta:

DELETE FROM member  
WHERE id IN (SELECT * 
             FROM (SELECT id FROM member 
                   GROUP BY member_id, quiz_num, question_num, answer_num HAVING (COUNT(*) > 1)
                  ) AS A
            );

Answer 3

Como se señaló en los comentarios, la consulta en la respuesta de Saharsh Shah debe ejecutarse varias veces si los elementos se duplican más de una vez.

Aquí hay una solución que no elimina ningún dato, y mantiene los datos en la tabla original todo el tiempo, lo que permite que los duplicados se eliminen mientras se mantiene la tabla ''activa'':

alter table tableA add column duplicate tinyint(1) not null default ''0'';

update tableA set
duplicate=if(@member_id=member_id
             and @quiz_num=quiz_num
             and @question_num=question_num
             and @answer_num=answer_num,1,0),
member_id=(@member_id:=member_id),
quiz_num=(@quiz_num:=quiz_num),
question_num=(@question_num:=question_num),
answer_num=(@answer_num:=answer_num)
order by member_id, quiz_num, question_num, answer_num;

delete from tableA where duplicate=1;

alter table tableA drop column duplicate;

Esto básicamente verifica si la fila actual es la misma que la última y, si lo está, la marca como duplicada (la declaración de orden asegura que los duplicados se mostrarán uno al lado del otro). Luego borras los registros duplicados. Elimino la columna duplicate al final para que vuelva a su estado original.

Parece que alter table ignore también podría desaparecer pronto: http://dev.mysql.com/worklog/task/?id=7395

Answer 4

En lugar de drop table TableA , puede eliminar todos los registros ( delete from TableA; ) y luego completar la tabla original con los registros que provienen de TableA_Verify ( insert into TAbleA select * from TAbleA_Verify ). De esta forma, no perderá todas las referencias a la tabla original (índices, ...)

CREATE TABLE TableA_Verify AS SELECT DISTINCT * FROM TableA;

DELETE FROM TableA;

INSERT INTO TableA SELECT * FROM TAbleA_Verify;

DROP TABLE TableA_Verify;

Answer 5

Esto no usa tablas TEMP, sino tablas reales. Si el problema es solo acerca de las tablas temporales y no sobre la creación de tablas o la eliminación de tablas, esto funcionará:

SELECT DISTINCT * INTO TableA_Verify FROM TableA;

DROP TABLE TableA;

RENAME TABLE TableA_Verify TO TableA;

Answer 6

Gracias a jveirasv por la respuesta anterior.

Si necesita eliminar duplicados de un conjunto específico de columnas, puede usar esto (si tiene una marca de tiempo en la tabla que varía, por ejemplo)

CREATE TABLE TableA_Verify AS SELECT * FROM TableA WHERE 1 GROUP BY [COLUMN TO remove duplicates BY];

DELETE FROM TableA;

INSERT INTO TableA SELECT * FROM TAbleA_Verify;

DROP TABLE TableA_Verify;

Answer 7

Si no está utilizando ninguna clave principal, luego ejecute las siguientes consultas de una sola vez. Al reemplazar valores:

# table_name - Your Table Name
# column_name_of_duplicates - Name of column where duplicate entries are found

create table table_name_temp like table_name;
insert into table_name_temp select distinct(column_name_of_duplicates),value,type from table_name group by column_name_of_duplicates;
delete from table_name;
insert into table_name select * from table_name_temp;
drop table table_name_temp

1. crear tablas temporales y almacenar valores distintos (no duplicados)
2. hacer la tabla original vacía
3. insertar valores en la tabla original desde la tabla temporal
4. eliminar la tabla de temp

Siempre es recomendable tomar una copia de seguridad de la base de datos antes de jugar con ella.

Answer 8

Una forma alternativa sería crear una nueva tabla temporal con la misma estructura.

CREATE TABLE temp_table AS SELECT * FROM original_table LIMIT 0

Luego crea la clave primaria en la tabla.

ALTER TABLE temp_table ADD PRIMARY KEY (primary-key-field)

Finalmente copie todos los registros de la tabla original mientras ignora los registros duplicados.

INSERT IGNORE INTO temp_table AS SELECT * FROM original_table

Ahora puede eliminar la tabla original y cambiarle el nombre a la nueva tabla.

DROP TABLE original_table
RENAME TABLE temp_table TO original_table