Shulou(Shulou.com)06/01 Report--

This article will explain in detail only one method for mysql to achieve duplicate check. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Mysql realizes the method of only one duplicate check: first, look up the redundant duplicate records in the table through "select * from"; then delete the duplicate data through "delete from" and keep only one data.

Mysql removes duplicate data and retains only one record

Delete duplicate data to keep the record with the lowest id in the 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 on the GROUP BY clause is similar to the way WHERE and SELECT interact. The WHERE search condition is applied before the grouping operation, while the HAVING search condition is applied after the grouping operation. The HAVING syntax is similar to the WHERE syntax, but HAVING can contain aggregate functions. The HAVING clause can refer to any item displayed in the selection list.)

Extend:

SQL: delete duplicate data, keep only one item using SQL statement, delete duplicate items and keep only one entry in thousands of records, there are some identical records, how can you use SQL statements to delete duplicates?

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 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 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)

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

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

7. Eliminate the first bit on the right of a field:

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

8. False deletion of 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 only way to check duplicates in mysql is shared here. I hope the above content can be helpful to everyone and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.