SQL deletes duplicate data
This article provides a mysql statement for deleting duplicate data, that is, there are more than two duplicate data records in the data table, and the information with the smallest number is deleted.
Delete 'borough _ name' as a from fke_borough as,
(
Select *, min (id) from fke_borough group by 'borough _ name' having count (1)> 1
) As B
Where a. 'borough _ name' = B. 'borough _ name' and a. id> B. id;
SELECT count (*)
FROM 'fke _ borough '// 3423
SELECT * FROM 'fke _ borough 'group by 'borough _ name' HAVING count ('borough _ name')> 1 // repeat 591 entries
Query duplicate records select * from table group by name
Only display repeated items
Select *, count (*) as nums from tab_a group by name having nums> 1
Method 1: (this method is good, but the auto-increment field will be rebuilt)
Create a temporary table
Create table tmp as select * from youtable group by name
Delete the original table
Drop table youtable
Rename a table
Alter table tmp rename youtable