Mysql statement to delete duplicate data, retain one; query all duplicate data; query duplicate data, mysql statement
// Display all duplicate entries
SELECT * FROM Table Name
WHERE (Field 1, Field 2 ,...)
IN (SELECT Field 1, Field 2,... FROM table name group by field 1, Field 2,... having count (*)> 1)
// Only one of the duplicates is displayed
SELECT * FROM table name group by concat (Field 1, Field 2,...) having count (*)> 1
// Delete duplicate data (retain the least ID)
Delete from Table Name
WHERE (Field 1, Field 2 ,...)
IN (SELECT Field 1, Field 2,... FROM
(SELECT Field 1, Field 2,... FROM table name group by field 1, Field 2,... having count (*)> 1) AS B
)
AND Id not in (SELECT id AS id FROM
(Select min (Id) AS id FROM table name group by field 1, Field 2,... having count (*)> 1) AS c
)
Note: For more details about the statement used to delete duplicate data, you must repeat the from statement to report an error.