MySQL queries several SQL statements with repeated records in the database

1. Query duplicate records based on a single field (name) in the table

SELECT * FROM user WHERE name IN (SELECT name FROM user group by name having count (name)> 1 );

2. Delete duplicate records based on a single field (name) in the table. Only records with the minimum id are retained.

Delete from user WHERE name IN (SELECT name FROM user group by name having count (name)> 1) AND id not in (SELECT min (id) FROM user group by name having count (name)> 1 );

3. Query duplicate records based on multiple fields (name, age) in the table

SELECT * FROM user u WHERE (u. name, u. age) IN (SELECT name, age FROM user group by name, age having count (*)> 1 );

4. Delete duplicate records based on multiple fields (name, age) in the table. Only records with the minimum id are retained.

Delete from user u WHERE (u. name, u. age) IN (SELECT name, age FROM user group by name, age having count (*)> 1) AND id not in (SELECT min (id) FROM user group by name, age having count (*)> 1 );