SQL: delete duplicate data. Only one record is retained.

SQL: delete duplicate data. Only one record is retained.

SQL2010-09-10 16:03:33 Read82 Comment0 Font Size:LargeMediumSmall Subscription

There are some 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. duplicate records are determined based on a single field (peopleid, only records with the smallest rowid are 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) 2.1 Delete from db_cr_maininfo for multiple column conditions
where cr_id not in
(
select Min (cr_id) from db_cr_maininfo where addtime> '2017-10-08'
group by communityname, address, activitiesstarttime, activitiesendtime
)
and addtime> '2017-10-08 '3. Search for redundant duplicate records in the table (multiple fields)
select * From vitae A
where (. peopleid,. 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. false deletion of redundant duplicate records (multiple fields) in the table ), records that do not contain the smallest rowid
Update vitae set ispass =-1
where peopleid in (select peopleid from vitae group by leleid, 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)