Only one SQL statement is retained to delete duplicate data.

With SQL statements, deleting repeated items only keeps one record in thousands of records, and some identical records exist. How can I use SQL statements, delete duplicate records 1. Search for redundant duplicate records in the Table. duplicate records are used to determine the select * frompeoplewherepeopleIdin (selectpeopleIdfrompeoplegro

With SQL statements, deleting repeated items only keeps one record in thousands of records, and some identical records exist. How can I use SQL statements, delete duplicate records 1. Search for redundant duplicate records in the Table. duplicate records are determined based on a single field (peopleId). select * from people where peopleId in (select peopleId from people gro

Use SQL statements to delete duplicate items and retain only one

There are several identical records in thousands of records. How can I use SQL statements to delete duplicates?

1. Search for redundant duplicate records in the Table. duplicate records are determined based on a single field (peopleId ).

Select * from people

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

2. Delete unnecessary duplicate records in the Table. Repeat records are determined based on a single field (eagleid), 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. Search for redundant duplicate records in the table (multiple fields)

Select * from vitae

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

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

Delete from vitae

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. Search for redundant duplicate records (multiple fields) in the table, excluding records with the smallest rowid

Select * from vitae

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. Remove the first place on the left of a field:

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

7. Remove the first digit on the right of a field:

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

8. The redundant duplicate records (multiple fields) in the table are deleted without the records with the smallest rowid.

Update vitae set ispass =-1

Where peopleId in (select peopleId from vitae group by peopleId