mysql - seleccionar - Eliminar filas duplicadas de la tabla con join

no mostrar registros duplicados sql

Question

Tengo dos tablas para contener state (state_table) y city (city_table) de países

La tabla de la ciudad tiene state_id para relacionarla con state_table

Ambas tablas ya tienen datos en ella.

Ahora el problema

La tabla de ciudad contiene múltiples entradas de una ciudad dentro de un estado. Y otras ciudades pueden tener o no el mismo nombre de ciudad

por ejemplo: cityone tendrá 5 ocurrencias en la tabla city con stateone y 2 ocurrencias con statetwo

Entonces, ¿cómo escribiré una consulta para mantener una ciudad para cada estado y eliminar el resto?

Schema sigue

CREATE TABLE IF NOT EXISTS `city_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `state_id` int(11) NOT NULL,
  `city` varchar(25) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `state_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `state` varchar(15) NOT NULL,
  `country_id` smallint(5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Esta es la información de muestra

id   state_id   city
1   1   city_one
2   1   city_two
3   1   city_one
4   1   city_two
5   2   city_one
6   3   city_three
7   3   city_one
8   3   city_three
9   4   city_four
10  4   city_five

La tabla original tiene 152,451 filas

Answer 1

Si desea eliminar la ciudad duplicada con el mismo state_id (registros duplicados), puede hacerlo al agruparlos por city y state_id y usar la función MIN o MAX :

Antes de eliminar la consulta, su tabla se veía como

| ID | STATE_ID |       CITY |
------------------------------
|  1 |        1 |   city_one |
|  2 |        1 |   city_two |
|  3 |        1 |   city_one |
|  4 |        1 |   city_two |
|  5 |        2 |   city_one |
|  6 |        3 | city_three |
|  7 |        3 |   city_one |
|  8 |        3 | city_three |
|  9 |        4 |  city_four |
| 10 |        4 |  city_five |

Puede usar la siguiente consulta para eliminar registros duplicados:

DELETE city_table 
  FROM city_table
  LEFT JOIN 
  (SELECT MIN(id) AS IDs FROM city_table
   GROUP BY city,state_id
  )A
  ON city_table.ID = A.IDs
  WHERE A.ids IS NULL;

Después de aplicar la consulta anterior, su tabla se verá así:

| ID | STATE_ID |       CITY |
------------------------------
|  1 |        1 |   city_one |
|  2 |        1 |   city_two |
|  5 |        2 |   city_one |
|  6 |        3 | city_three |
|  7 |        3 |   city_one |
|  9 |        4 |  city_four |
| 10 |        4 |  city_five |

Ver este SQLFiddle

Para más información, consulte DELETE Syntax of MySQL.

Answer 2

DELETE FROM city_table 
WHERE id NOT IN 
  (SELECT MIN(id) 
   FROM city_table 
   GROUP BY state_id, city)

Si encuentra que esta consulta es demasiado lenta, puede crear una tabla temporal y almacenar el resultado de la subconsulta en ella, luego truncar la tabla original y volver a llenar los contenidos. Es una solución un poco sucia, ya que tendría que establecer los valores de la columna auto_increment.