Record duplicate processing scheme in MySQL table

Today I wrote an airport table with a few fields

Primary key ID, airport English name, airport Chinese name, airport three codewords, Airport city name
ID, name, c_name, Code,city_name
Since the airport three codewords are not duplicated, I initially added a unique index to the airport table

ALTER TABLE ' airport ' ADD UNIQUE (' Code ');

But in the process of writing found that I crawled to the information there is duplication, so temporarily remove the unique index.

Show index from airport;//View index
DROP INDEX code on Airport
Up and down Reptile program code

<?php
Require (' phpquery.php ');
Phpquery::newdocumentfile ("http://****************** for some reason ***********");
$res = PQ (' tbody ')->find (' tr ')->text ();

INSERT into a table according to your business logic

Soon finished the work, after completion, still want to understand the three codewords repeat problem.

My approach was to take all the smallest three codewords records for deletion, with the earliest written code as follows:

DELETE from Airport WHERE
ID in (SELECT ID from Airport GROUP by code has COUNT (code) > 1)
and ID not in (SELECT Max (ID) from airport GROUP by code has COUNT (code) >1);

However, there was an error running this SQL because the deletion or update operation could not be synchronized at the time of the selection. It's time to refer to a temporary table.

Create temporary table TMP Select ID from Airport WHERE
ID in (SELECT ID from Airport GROUP by code has COUNT (code) > 1)
and ID not in (SELECT Max (ID) from airport GROUP by code has COUNT (code) >1);

Delete from airport where ID in (SELECT ID from TMP)
OK, the operation is complete, and then see if there is any duplication:

SELECT ID from Airport GROUP by code has COUNT (code) > 1

If there are any further deletions. The three codewords of the table have not been repeated, then add a unique index to the three codewords ...