SQL Delete duplicate data keeps only one bar

http://blog.csdn.net/anya/article/details/6407280/

With SQL statements, delete duplicates only keep one

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

1. Querying for Duplicate records

SELECT * FROM table name
where repeating field in (select repeating field from table name GROUP by repeating field having count (repeating field) > 1)

2. Delete Keep a duplicate record

Delete from table name
where repeating field in (Select repeating field from table name group by repeating field having count (repeating field) > 1)
and ID not in (select min (ID) from table name group by repeating field having count (repeating field) >1)

A while ago a member system, and wrote a program to import the previous member data into the SQL database. For some reason, several times, causing some duplicate records to exist. The two days before the use of personnel to find the problem, and then find a way to solve.
Search, search, find the way to delete duplicate records using SQL sentences. Once again I realized the power of SQL (actually my SQL level is too much). Write it down and strengthen the memory.
Membership data needs to be used in the following three fields: ID (self-increment), membername,memberaddress. As long as the member's name and membership address is the same as duplicate records, duplicate records in the deletion of only the largest number of the ID. SQL is as follows:

Delete MemberInfo where ID not in (
Select Max (ID) from MemberInfo Group by MemberName, Memberaddress)

The not-in may be less efficient, but it is not important because it is directly manipulating the database. The sentence is very simple and effective.

Before a real delete operation, you'll usually get a look at the duplicate record. You can use the following sentences:

SELECT COUNT (membername) as Thecount, MemberName, memberaddress
From MemberInfo
GROUP by MemberName, memberaddress
Having (COUNT (*) > 1)

Because the SQL used in the work is so simple that group by and having the usage are not understood, it's a shame.
P.S. All the content comes from the Internet, and there is nothing original about it. Sent out just to strengthen my memory.

SQL Delete duplicate data keeps only one bar