MySQL delete duplicate record SQL statement

Create a table that holds the ID information for the record you want to delete:

The code is as follows	Copy Code
CREATE TABLE ' Tmp_ids ' ( ' ID ' int (11), ' Name ' char (20) ) Engine=myisam;

If you want to delete a few records, you can create this table into a memory table Form:

The code is as follows	Copy Code
CREATE TABLE ' Tmp_ids ' ( ' ID ' int (11), ' Name ' char (20) ) Engine=heap;

Then delete the duplicate record in the test table:

The code is as follows	Copy Code
Insert INTO Tmp_ids select min (id), name from test group by name has COUNT (*) >1 order by NULL; Delete a.* from test A,tmp_ids b where B.name=a.name and a.id>b.id; TRUNCATE TABLE tmp_ids;

Method Two

Copy no duplicates to the new table, delete the old table, and then rename the new table to the old table name.

The code is as follows

Copy Code

Mysql> SELECT * from duplicate where ID (select min (id) to duplicate group by name); +----+-------+ | ID | name | +----+-------+ | 1 | Wang | | 3 | Wdang | | 5 | Wdand | | 6 | Wddda | +----+-------+ 4 rows in Set (0.01 sec) Mysql> CREATE TABLE Duplica select * from duplicate where ID in (select min (id) to duplicate group by name); Query OK, 4 rows Affected (0.02 sec) Records:4 duplicates:0 warnings:0 mysql> drop table Duplicate; Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE Duplica Rename to duplicate; Query OK, 0 rows Affected (0.00 sec) Mysql> select * from duplicate; +----+-------+ | ID | name | +----+-------+ | 1 | Wang | | 3 | Wdang | | 5 | Wdand | | 6 | Wddda | +----+-------+ 4 rows in Set (0.00 sec) Mysql> ALTER TABLE duplicate modify ID int (2) NOT null primary key auto_increment; Query OK, 4 rows Affected (0.00 sec) Records:4 duplicates:0 warnings:0

Then I thought of a statement to fix it:

The code is as follows

Copy Code

mysql> use MySQL Database changed mysql> select * FROM duplicate +----+-------+ | id | name  | +----+-------+ |  1 | wang  | |  3 | wdang | |  5 | wdand | |  6 | wddda | |  2 | wang  | |  4 | wdang | +----+-------+ 6 rows in Set (0.00 sec) Mysql> Delete Duplicate as a from duplicate as a,     -> (    -> SELECT * from duplicate group by name has count (1) >1) as B  &nb sp; -> where a.name=b.name and a.id > b.id; Query OK, 2 rows Affected (0.00 sec) Mysql> select * FROM duplicate +----+-------+ | id | name  | +----+-------+ |  1 | wang  | |  3 | wdang | |  5 | wdand | |  6 | wddda | +----+-------+ 4 rows in Set (0.00 sec)

Keep the record with the smallest ID.

The 1th kind:

The code is as follows	Copy Code
Delete from%s where Goodsurl in (select Goodsrul as Gurl1 from%s #group by GRUL1 had count (GURL1) >1) Rs1 and ID not in (select min (id) as Id2 from%s #group by Goodsurl has count (goodsurl) >1) rs2 "% (a,a,a)

2nd: This method does not use subsets, but I do not know how to embed the rs1,rs2 inside the SQL statement,

code is as follows	copy code
Exesql = " Select min (id) from%s GROUP by Goodsurl Havingcount (goodsurl) >1) "% (A,) Cur.execute (exesql) Rs1 = Cur.fetchall () Exesql = "Select Goodsurl from%s Group by Goodsurl Havingcount (goodsurl) >1"% (A,) Cur.execute (exesql) RS 2 = Cur.fetchall () Exesql = "Delete from%s where Goodsurl in%s and ID.%"% (a,rs2,rs1) Cur.execute (Exesql) /p>