How to delete duplicate data in SQL

In SQL, you can use the select statement to delete 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 keep only one

There are some identical records among thousands of records. How can I use SQL statements to delete duplicates

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

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

Extension:

Delete redundant duplicates in the table Records, duplicate records are judged based on a single field (peopleId), only the record with the smallest rowid is left

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)

Find the redundant duplicate records (multiple fields) in the table

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

Delete the table Redundant duplicate records (multiple fields), only the record with the smallest rowid is left

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)

Excessive duplicate records (multiple fields) in the lookup table, 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)

Eliminate the first digit on the left of a field:

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

Eliminate the first digit on the right of a field:

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

False Delete redundant duplicate records (multiple fields) in the table, not included The record with the smallest rowid

update vitae set ispass=-1where peopleId in (select peopleId from vitae group by peopleId

Related recommendations: "mysql tutorial"

The above is the detailed content of How to delete duplicate data in SQL. For more information, please follow other related articles on the PHP Chinese website!