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

This article shows you how to use SQL sentences to delete duplicate records and keep only one entry, the content is concise and easy to understand, can definitely brighten your eyes, through the detailed introduction of this article, I hope you can get something.

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

The copy code is as follows:

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.

The copy code is as follows:

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)

The copy code is as follows:

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

The copy code is as follows:

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

The copy code is as follows:

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:

The copy code is as follows:

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

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

The copy code is as follows:

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

The copy code is as follows:

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 content is how to use SQL statements to delete duplicate records and keep only one entry. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.