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> |