How to delete duplicate records in MySQL

MySQL how to delete duplicate records, I believe that many inexperienced people do not know what to do, so this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

1. Find all duplicate records

Select * from table where repeat field in (select repeat field from table group by repeat field having count (*) > 1)

2. Filter duplicate records (only one entry is displayed)

Select * from HZT Where ID In (select max (ID) from HZT group by Title)

Note: the largest record of ID is shown here.

Delete duplicate record

1. Delete all duplicate records (use with caution)

Delete table where repeat field in (select repeat field from table group by repeat field having count (*) > 1)

2. Keep one (this should be what most people need ^ _ ^)

Delete HZT where ID not In (select max (ID) from HZT group by Title)

Note: the largest record of ID is kept here.

Third, give examples

1. Look up the redundant duplicate records in the table. Duplicate records are judged by a single field (peopleId).

Select * from people where peopleId in (select peopleId from people group by peopleId having count (peopleId) > 1)

2. Delete redundant duplicate records from the table. Duplicate records are judged according to a single field (peopleId), leaving only the records with the smallest rowid.

Delete from people where peopleId in (select peopleId from people group by peopleId having count (peopleId) > 1) and rowid not in (select min (rowid) from people group by peopleId having count (peopleId) > 1)

3. Look up redundant duplicate records in the table (multiple fields)

Select * from vitae a where (a.peopleIdmema.seq) in (select peopleId,seq from vitae group by peopleId,seq having count (*) > 1)

4. Delete the redundant duplicate records (multiple fields) in the table, leaving only the record with the smallest rowid

Delete from vitae a where (a.peopleIdline a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count (*) > 1) and rowid not in (select min (rowid) from vitae group by peopleId,seq having count (*) > 1)

5. Look up the redundant duplicate records in the table (multiple fields), excluding the records with the smallest rowid

Select * from vitae a where (a.peopleIdmeme a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count (*) > 1) and rowid not in (select min (rowid) from vitae group by peopleId,seq having count (*) > 1)

IV. Supplement

There are more than two duplicate records, one is a completely duplicate record, that is, a record in which all fields are duplicated, and the other is a record in which some key fields are duplicated, such as the Name field, while other fields are not necessarily duplicated or can be ignored.

1. For the first kind of repetition, it is easier to solve and use

Select distinct * from tableName

You can get a result set with no duplicate records.

If the table needs to delete duplicate records (keep 1 duplicate record), you can delete it as follows

Select distinct * into # Tmp from tableName drop table tableName select * into tableName from # Tmp drop table # Tmp

The reason for this repetition is the poor design of the table, which can be solved by adding unique index columns.

2. This kind of repetition problem usually requires the retention of the first record in the duplicate record, as follows.

Suppose a duplicate field is Name,Address, and a unique result set of these two fields is required.

Select identity (int,1,1) as autoID, * into # Tmp from tableName select min (autoID) as autoID into # Tmp2 from # Tmp group by Name,autoID select * from # Tmp where autoID in (select autoID from # tmp2) after reading the above, do you know how to delete duplicate records in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!