MySQL Delete duplicate record, keep the one with the smallest ID

MySQL query repeating fields, and how to delete duplicate records
MySQL, database, Database, field, server
There is a large table in the database and you need to look for a duplicate record ID for the name in order to compare it. If you are simply looking for a word that is not duplicated in the database , it is easy to:
SELECT min (' id '), ' name ' from ' table ' GROUP by ' name ';

However, this does not give the ID value of the repeating field. (only the smallest ID value is obtained) it is easy to query which fields are duplicates:
SELECT ' name ', Count (' name ') as Count from ' table ' GROUP by ' name ' has count (' name ') >1 ORDER by Count DESC;

But to query the ID value of a repeating field at once, you must use the subquery, and use the following statement.
SELECT ' id ', ' name ' from ' table ' WHERE ' name ' in (
SELECT ' name '
From ' table '
GROUP by ' name ' has count (' name ') >1);

But this statement is inefficient in MySQL, and it feels like MySQL has not generated a zero table for subqueries. Then use the first set to zero table:
CREATE TABLE ' tmptable ' as (
SELECT ' name '
From ' table '
GROUP by ' name ' has count (' name ') >1);

Then use the multi-table join query:
SELECT A. ' ID ', a. ' Name ' from ' table ' A, ' tmptable ' t WHERE a. ' Name ' = T. ' Name ';

The result came out soon.
========================
Ways to query and delete duplicate records
A
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 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)

(The above statement is executed in MySQL with an error:

Execution error: 1093-you can ' t specify target table ' student ' for update in FROM clause
The reason is that the query is used when updating the data, and the data of the query is updated again, andMySQL does not support this approach. Both Oracel and msserver support this kind of square formula.
How to circumvent this problem?
Plus a layer of encapsulation,

Delete from Php_user where
Username in (select username from (select username to Php_user GROUP by username have count (username) >1) A
The and ID not in (the select min (id) from (the Select min (id) as ID from the Php_user Group by username have the count (U Sername) >1) b)

Note Select min (ID) is followed by an as ID.

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)

Two
Say
There is a field "name" in the a table,
And the "name" values between the different records are likely to be the same,
Now is the need to query out the records in the table, "name" values there are duplicates;
Select Name,count (*) from A Group by Name have Count (*) > 1

If you also look at the same gender, the following is true:
Select Name,sex,count (*) from A Group by Name,sex have Count (*) > 1

Three
Method One

Declare @max integer, @id integer

Declare cur_rows cursor Local for select main field, COUNT (*) from table name Group by main field having count (*) >; 1

Open Cur_rows

Fetch cur_rows into @id, @max

While @ @fetch_status =0

Begin

Select @max = @max-1

SET ROWCOUNT @max

Delete from table name where main field = @id

Fetch cur_rows into @id, @max

End

Close Cur_rows

SET ROWCOUNT 0

Method Two

There are two meanings of duplicate records, one is a completely duplicate record, that is, all the fields are duplicated records, and the second is a part of the keyword segment duplicate records, such as the Name field is repeated, and the other fields may not be repeated or repeated can be ignored.

1, for the first kind of repetition, easier to solve, using

SELECT DISTINCT * from TableName

You can get a result set with no duplicate records.

If the table needs to delete duplicate records (duplicate records retain 1), you can delete them as follows

SELECT DISTINCT * to #Tmp from TableName

drop table TableName

SELECT * Into TableName from #Tmp

drop table #Tmp

This duplication occurs because the table is poorly designed and the unique index columns are added to resolve.

2, this kind of repetition problem usually requires to keep the first record in the duplicate record, the operation method is as follows

Suppose there is a duplicate field name,address, which requires the result set to be unique for both fields

Select Identity (int,1,1) as Autoid, * into #Tmp from TableName

Select min (autoid) as autoid into #Tmp2 from #Tmp Group by name,autoid

SELECT * from #Tmp where autoid on (select Autoid from #tmp2)

The last select is the result set that name,address not duplicate (but one more autoid field, which can be written in the SELECT clause without this column in the actual write)

Four
Duplicate query

SELECT * FROM tablename where ID in (

Select ID from tablename

GROUP BY ID

Having count (ID) > 1

)

ref:http://www.greensoftcode.net/techntxt/2013120131251921893458
Original: http://bbs.ahpal.com/thread-3585-1-1.html

MySQL deletes duplicate records and retains the lowest ID of the article