In thousands of records, there are some same records, how can you 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 People
where Peopleid in (select Peopleid from People GROUP by Peopleid have count (Peopleid) > 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 from people
where Peoplename in (select Peoplename from People GROUP by Peoplename have count (peoplename) > 1)
and Peopleid not in (select min (peopleid) from people GROUP by Peoplename have Count (peoplename) >1)
3. Find 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 have count (*) > 1)
4. Delete extra duplicate records (multiple fields) in the table, leaving only the record with ROWID minimum
Delete from Vitae a
where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1)
and rowID not in (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1)
5. Find redundant duplicate records (multiple fields) in the table, not including the smallest ROWID records
SELECT * FROM Vitae a
where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1)
and rowID not in (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1) 6. Remove the first bit to the left of a field: Update TableName Set [Title]=right ([title], (Len ([title])-1)) where Title like ' Village% ' 7. Remove the first bit to the right of a field: Update TableName Set [Title]=left ([title], (Len ([title])-1)) where Title like '% Village ' 8. False Delete extra duplicate records (multiple fields) in the table, not including ROWID minimum records
Update vitae set Ispass=-1
where Peopleid in (select Peopleid from Vitae GROUP by Peopleid |
