SQL statements Delete duplicate records and retain only one record.

SQL statements Delete duplicate records and retain only one record.

Copy codeThe Code is as follows:
Delete WeiBoTopics where Id in (select max (Id) from WeiBoTopics group by WeiBoId, Title having COUNT (*)> 1 );

SQL: delete duplicate data. Only one SQL statement is retained. If you delete duplicate items, only one record is retained in thousands of records. The same record exists. How can you use SQL statements, delete duplicate items.

1. Search for redundant duplicate records in the Table. duplicate records are determined based on a single field (peopleId ).
Copy codeThe Code is as follows:
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
Copy codeThe Code is as follows:
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)
Copy codeThe Code is as follows:
Select * from vitae a 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
Copy codeThe Code is as follows:
Delete from vitae a where (. peopleId,. 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
Copy codeThe Code is as follows:
Select * from vitae a where (. peopleId,. 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:
Copy codeThe Code is as follows:
Update tableName set [Title] = Right ([Title], (len ([Title])-1) where Title like 'village %'

7. Remove the first digit on the right of a field:
Copy codeThe Code is as follows:
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.
Copy codeThe Code is as follows:
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)