Shulou(Shulou.com)05/31 Report--

This article will explain in detail the methods of deleting duplicate data in SQL. 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.

In sql, you can use the select statement to remove duplicate data. The syntax is: "select * from field where field id in (select field id from field group by field having count (field id) > 1)".

The operating environment of this tutorial: windows7 system, mysql8.0 version, Dell G3 computer.

Use SQL statements to delete duplicates and leave only one

In thousands of records, there are some of the same records, how can you use SQL statements to delete duplicates?

Look for redundant duplicate records in the table, which are judged by a single field (peopleId)

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

Extend:

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

Delete from peoplewhere 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)

Find redundant duplicate records in the table (multiple fields)

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

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

Delete from vitae awhere (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)

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

Select * from vitae awhere (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)

Eliminate the first bit on the left of a field:

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

Eliminate the first bit on the right of a field:

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

False deletion of redundant duplicate records (multiple fields) in the table, excluding the record with the smallest rowid

This is the end of update vitae set ispass=-1where peopleId in (select peopleId from vitae group by peopleId's article on "how to remove duplicate data in SQL". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please 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.