Mysql statement to delete duplicate data, retain one; query all duplicate data; query duplicate data, mysql statement

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.