Alejandro Muñoz Alejandro Muñoz - 5 months ago 14
MySQL Question

delete duplicate rows mysql not unique

I have a little problem with database, charge the regisrtos from xls and can load more than two vcese the same xls generating duplicate records, I can not restrict that are unique because they are records that are repeated in several columns ... I meuestro them

id| Fecha | EquipoA | EquipoB | Categoria | Cancha | Hora | Nr_J1 | Nombre_J1 | Apellido_J1 | Nr_J2 | Nombre_J2 | Apellido_J2
1 |Sáb 09 Abr | San Juan | Edu | sub18 | San Juan | 15:00 | 39 | A. | Aunchayna | 95 | S. | Villizio
2 |Sáb 09 Abr | San Juan | Impulso | sub16 | San Juan | 16:30 | 39 | A. | Aunchayna | 95 | S. | Villizio
3 |Sáb 09 Abr | San Juan A| ISASA | sub16 | San Juan | 18:00 | 39 | A. | Aunchayna | 95 | S. | Villizio
4 |Sáb 09 Abr | San Juan | ISASA | sub14 | San Juan | 19:30 | 39 | A. | Aunchayna | 95 | S. | Villizio
5 |Sáb 09 Abr | San Juan | Edu | sub18 | San Juan | 15:00 | 39 | A. | Aunchayna | 95 | S. | Villizio


this is an example register 1 is equal to 5
in all columns, but others differ in EquipoA or EquipoB or Hora...
so I can not make them be unique

I need to clear the log is repeated in this case 5 and leave 1
I'm no expert in MySql and I was very difficult to find the right query

Answer

This will delete exact duplicates (except of id column):

delete t1
from Table1 t1
join Table1 t2 using (
  `Fecha`,
  `EquipoA`,
  `EquipoB`,
  `Categoria`,
  `Cancha`,
  `Hora`,
  `Nr_J1`,
  `Nombre_J1`,
  `Apellido_J1`,
  `Nr_J2`,
  `Nombre_J2`,
  `Apellido_J2`
)
where t1.id > t2.id

Demo

Comments