Delete repeated records from a table in MySQL

Question

Delete repeated records from a table in MySQL

Navigation

#1 by (5 votes)
#2 by (1 votes)

4

I have managed to show you how many data I have repeated but now I want to make a query to delete the records that have 5 repeated data: student_id, id_curso, semester finalnote and id_seccion.

select  count(*),
            uni_notas.id_alumno,
            uni_notas.id_curso,
            asd_personas.nombre,
            uni_notas.notafinal,'introducir el código aquí'
            uni_secciones.codigo,
            uni_cursos.codigo,
            uni_cursos.nombre,
            uni_notas.semestre
from uni_notas 
          inner join uni_cursos
                on uni_cursos.id = uni_notas.id_curso
          inner join uni_alumnos 
                on uni_notas.id_alumno = uni_alumnos.id
          inner join asd_personas
                on asd_personas.id = uni_alumnos.id_persona
          inner join uni_secciones 
                on uni_secciones.id = uni_notas.id_seccion
where uni_cursos.codigo in (select uni_cursos.codigo from uni_cursos where uni_cursos.codigo LIKE '%35%')
          group by id_alumno, id_curso, notafinal, semestre, id_seccion
                having count(1)>1

The script to create the original table is:

CREATE TABLE IF NOT EXISTS uni_notas ( 
    id int(11) NOT NULL
  , id_curso int(11) DEFAULT NULL
  , id_seccion int(11) DEFAULT NULL
  , id_alumno int(11) DEFAULT NULL
  , creditos decimal(10,2) DEFAULT NULL
  , semestre char(6) COLLATE utf8_unicode_ci DEFAULT NULL
  , asistencia int(3) DEFAULT NULL
  , zona int(3) DEFAULT NULL
  , siglas char(3) COLLATE utf8_unicode_ci DEFAULT NULL
  , exfinal int(3) DEFAULT NULL
  , notafinal int(3) DEFAULT NULL
  , recupera int(3) DEFAULT NULL
  , tipo char(1) COLLATE utf8_unicode_ci DEFAULT NULL
  , estado int(1) DEFAULT '1' 
)

mysql

asked by Cris Valdez 07.09.2016 в 00:02

source

2 answers

1

In this case, I think the best thing you can do is multiply the table by itself according to the 5 fields you want, and eliminate all ids other than those selected in the table on the left of the Cartesian product.

    DELETE FROM uni_notas U1 JOIN uni_notas U2 ON 
     (
       U1.id_alumno = U2.id_alumno AND 
       U1.id_curso = U2.id_curso AND 
       ...
     ) WHERE U2.id <> U1.id

In what versions of MySQL you can give an error when selecting and deleting from the same table, if it is your case, create a temporary table with the ids of the notes that must be deleted and then execute a:

DELETE FROM uni_notas WHERE id IN (SELECT * FROM temp_table)

answered by 07.09.2016 в 16:50

Can I delete these files without anything happening? Problems with button of google design material

score 5 · Accepted Answer

If you are using MySQL 5.7.3 or lower and you do not have preferences about which repeated uni_notas logs to stay, one possibility is to create a unique index strong> using the IGNORE extension on the fields you mention.

ALTER IGNORE TABLE 'uni_notas'   
ADD UNIQUE INDEX ('id_alumno', 'id_curso', 'semestre', 'notafinal' y 'id_seccion');

Again, this solution is valid if you have the appropriate version of MySQL (not for nothing has the extension been removed) and you do not care which duplicate you stay.

If you have any criteria to keep one or another record, then the solution to your problem comes from something more elaborate. And the first thing you should indicate is what would be said criterion.