How to implement duplication checking in mysql and only leave one

Mysql method to implement duplicate checking and keep only one: first find the redundant duplicate records in the table through "select * from"; then delete the duplicate data through "delete from" and keep only one data.

Recommended: "mysql video tutorial"

mysql deletes duplicate data and keeps only one record

Delete duplicate data and keep the record with the smallest id in name

delete from order_info where id not in (select id from (select min(id) as id from order_info group by order_number) as b);

delete from table where id not in (select min(id) from table group by name having count(name)>1) and  id in (select id group by name having count(name)>1)

(Note: The way the HAVING clause sets conditions for the GROUP BY clause is similar to the way WHERE and SELECT interact. WHERE Search conditions are applied before the grouping operation is performed; HAVING search conditions are applied after the grouping operation is performed. The HAVING syntax is similar to the WHERE syntax, but HAVING can contain aggregate functions. The HAVING clause can reference any item displayed in the select list.)

Extension:

SQL: Delete duplicate data and keep only one SQL statement. Delete duplicates and keep only one among thousands of records. , there are some identical records, how can we use SQL statements to delete duplicates?

1. Find the redundant duplicate records in the table. Duplicate records are based on a single field (peopleId) To judge

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

2. Delete redundant duplicate records in the table. Duplicate records are judged based on a single field (peopleId), leaving only the record with the smallest rowid

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

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

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

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

delete from vitae a where (a.peopleId,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 table Redundant duplicate records (multiple fields), excluding the record with the smallest rowid

select * from vitae a where (a.peopleId,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)

6. Eliminate the first digit on the left of a field:

update tableName set [Title]=Right([Title],(len([Title])-1)) where Title like '村%'

7. Eliminate the first digit on the right of a field First place:

update tableName set [Title]=left([Title],(len([Title])-1)) where Title like '%村'

8. Fakely delete redundant duplicate records (multiple fields) in the table, excluding the record with the smallest rowid

update vitae set ispass=-1 where peopleId in (select peopleId from vitae group by peopleId,seq having count(*) > 1) and seq in （select 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)

The above is the detailed content of How to implement duplication checking in mysql and only leave one. For more information, please follow other related articles on the PHP Chinese website!