"Go" SQL deletes duplicate records, leaving only one of them

SQL: Delete duplicate data, keep only one with SQL statement, delete duplicates only keep one in thousands of records, there are some identical records, how to use SQL statements, delete duplicates

1, look for redundant records in the table, duplicate records are based on a single field (Peopleid) to determine

Select *  from where inch (selectfromgroupby hascount>1 )

2, delete redundant records in the table, duplicate records are based on a single field (Peopleid) to judge, leaving only the smallest ROWID records

Delete  fromPeoplewherePeoplenameinch(SelectPeoplename fromPeopleGroup  byPeoplename having Count(Peoplename)> 1) andPeopleid not inch(Select min(Peopleid) fromPeopleGroup  byPeoplename having Count(Peoplename)>1)

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

Select *  from where inch (selectfromgroupby havecount(*>  1)

4. Delete extra duplicate records (multiple fields) in the table, leaving only the record with ROWID minimum

Delete  fromVitae Awhere(A.PEOPLEID,A.SEQ)inch(SelectPeopleid,seq fromVitaeGroup  byPeopleid,seq having Count(*)> 1) androwID not inch(Select min(ROWID) fromVitaeGroup  byPeopleid,seq having Count(*)>1)

5. Find redundant duplicate records (multiple fields) in the table, not including the smallest ROWID records

Select *  fromVitae Awhere(A.PEOPLEID,A.SEQ)inch(SelectPeopleid,seq fromVitaeGroup  byPeopleid,seq having Count(*)> 1) androwID not inch(Select min(ROWID) fromVitaeGroup  byPeopleid,seq having Count(*)>1)

6. Remove the first bit to the left of a field:

Update Set [Title] =right ([title], (len([title])-1  wherelike' village%'

7. Remove the first bit to the right of a field:

Update Set [Title] =left ([title], (len([title])-1  wherelike'% village '

8. False Delete extra duplicate records (multiple fields) in the table, not including ROWID minimum records

 UpdateVitaeSetIspass=-1 wherePeopleidinch(SelectPeopleid fromVitaeGroup  byPeopleid,seq having Count(*)> 1) andSeqinch（SelectSeq fromVitaeGroup  byPeopleid,seq having Count(*)> 1） androwID not inch(Select min(ROWID) fromVitaeGroup  byPeopleid,seq having Count(*)>1)

This article transferred from: http://www.cnblogs.com/huangw/archive/2012/06/04/2534676.html

"Go" SQL deletes duplicate records, leaving only one of them