Ways to query and delete duplicate records

1, look for redundant records in the table, duplicate records are based on a single field (Peopleid) to determine the 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 Peopleid in (select Peopleid from People GROUP by Peopleid have count (Peopleid) > 1)
and rowID not in (select min (rowid) from people GROUP by Peopleid have Count (Peopleid) >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)

First look at the table myemp

Find records with duplicate data

Find records with no duplicate data

Identify records that are not duplicated

Or

SELECT * from Myemp e where rowid = (select Max (ROWID) from myemp e2 where E.userid = E2.userid and e.username = E2.userna Me and e.salary = e2.salary)

How to delete duplicate data

1, when there is a large number of duplicate data and there is an index on the column userid,username,salary

Delete myemp where rowID not in (select Max (ROWID) from Myemp Group by userid,username,salary);

2. For a small amount of repetitive data (when there is a large amount of data, the efficiency is very low)

Delete myemp e where rowID <> (select Max (ROWID) from myemp e2 where E.userid = E2.userid and e.username = E2.userna Me and e.salary = e2.salary);

3, exception method, suitable for a large number of data duplication situation

First build the Exception table

Then add the constraint and log the error to the table exceptions

Create a duplicate data staging table

Delete all data that has duplicates

Reinsert non-repeating data from the staging table into the original table

Ways to query and delete duplicate records