MySQL Delete duplicate data

Yesterday encountered a problem, it is necessary to delete a table of duplicate data, and there is the hope that the ID of this list can be continuous, because once the duplicate record is deleted, as the self-increment key ID will be discontinuous, so we need to find a way to fix

Table of data:

I want to remove the name repeatedly, but I also want the ID to be contiguous.

The first is to delete the duplicate records inside the database, I think there are many answers on the Internet:

1 Delete  frompeople2 wherePeopleidinch(SelectPeopleid fromPeopleGroup  byPeopleid having Count(Peopleid)> 1) 3  androwID not inch(Select min(ROWID) fromPeopleGroup  byPeopleid having Count(Peopleid)>1)

But in fact, every time I run this statement is not feasible, will be error:

SQL error [1093] [HY000]: You can ' t specify target table ' test1 ' for update on FROM clause
Java.sql.SQLException:You can ' t specify target table ' test1 ' for update on FROM clause

To check the Internet as if the update and delete operations have no way to do with the query operation, I have seen some updates with the query to do the same as the part of the individual name, and then update on it, but delete this I played an alias is not right, do not know whether I write wrong or not, I'll skip this method.

I used the method is: first to find out the database of duplicate records in one of the data, this is not difficult, very simple, the SQL statement is as follows:

Select *  fromTest1whereNameinch(SelectName fromTest1Group   byName having  Count(name)> 1)  andIdinch(Select min(ID) fromTest1Group  byName having Count(name)>1)

The results are as follows:

ID |name |phont |
---|--------|-------|
1 |name22 |123 |
3 |name222 |123 |
5 |name2 |123123 |
8 |123 |123123 |
|name1 |123123 |
13 |111 |1231 |
14 |112 |1232 |

These are not repeated, in other words, are to be retained, not deleted, and the rest of the results of the same name should be deleted.

That is, the top of the SQL statement ID plus a not, the result is to be deleted: The result is as follows

ID |name |phont |
---|--------|-------|
2 |name22 |123 |
4 |name222 |123 |
6 |name2 |123123 |
7 |name2 | NULL |
9 |123 |123123 |
10 |123 |123123 |
|name1 |123123 |
15 |111 |1233 |
16 |112 |1234 |
17 |111 |1235 |
18 |112 |1236 |

I'll save this to a different table, and then I'll create a new TEST2, structure to copy the TEST1 structure.

1 CREATE TABLE' Test2 ' (2' ID 'int( One) not NULLAuto_increment,3' Name 'varchar( -)DEFAULT NULL,4' Phont 'varchar( -)DEFAULT NULL,5   PRIMARY KEY(' id ')6) ENGINE=InnoDBDEFAULTCHARSET=Utf8

Then the INSERT statement is:

1 Insert  intoTest2 (2 Select *  fromTest.test1whereNameinch(SelectName fromTest.test1Group   byName having  Count(name)> 1) 3  andId not inch(Select min(ID) fromTest.test1Group  byName having Count(name)>1) 4)

And then the data in the Test2 table is this:

The next thing to do is to delete the same data in the Test1 table as the ID of the Test2 table.

1 Delete A.*fromwhere= b.id;

In this way, the data inside the test1 becomes:

The result is completely non-repetitive, but I also want their ID to be sequential, not so disconnected.

My practice is to isolate all fields outside the ID of this table into the other table test3, of course, test3 to set the ID to the self-increment primary key, but not insert ID, let it increment, continuous

Of course, to create a new table test3, but the new test2 on the top of the copy down to change the name of Test3 just fine.

Then insert:

1 Insert  into test3 (name, Phont) 2 (select from Test2)

The result of the Test3 table is:

So you can change test3 to the name you want, and then delete Test1 and test2.

But the feeling can also be deleted from the duplicate data table test1 data are all exported, the General database connection tool has such a function, in SQL format, and then create a new table, more than test1 a self-increment primary key field called newid field, but the ID field can no longer be self-increment, Then the SQL file will be imported, but the SQL file may have to edit, change the self-increment primary key ID into a normal field and so on, then to the new table, delete the ID field, modify NEWID as ID, it should also be possible, but this method I have not tried, Originally expected to do so but did not do so, it is estimated that you can try, but it feels like the two kinds of trouble is similar, but if you write down the SQL statement may be the first way to compare faster.

MySQL Delete duplicate data