Mysql SQL statement for deleting duplicate records

We often encounter the need to delete repeated records in a data table. Below I will summarize several methods that can delete Repeated Records and help to improve efficiency. For more information, see.

We often encounter the need to delete repeated records in a data table. Below I will summarize several methods that can delete Repeated Records and help to improve efficiency. For more information, see.

Create a table to store the id of the record to be deleted:

The Code is as follows:
Create table 'tmp _ kids '( 'Id' int (11 ), 'Name' char (20) ) ENGINE = MyISAM;

If there are not many records to delete, you can create the table as a memory table:

The Code is as follows:
Create table 'tmp _ kids '( 'Id' int (11 ), 'Name' char (20) ) ENGINE = HEAP;

Delete duplicate records in the test table:

The Code is as follows:
Insert into tmp_ids min (id), name from test name having 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 2

Copy duplicate records to the new table, delete the old table, and rename the new table as the old table name.

The Code is as follows:

Mysql> select * from duplicate where id in (select min (id) from 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) from 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 about a statement:

The Code is as follows:

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, -> ( -> Select * from duplicate group by name having count (1)> 1) as B -> 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)

Record with the smallest ID is retained.

1st types:

The Code is as follows:
Delete from % s where goodsurl in (select goodsrul as gurl1 from % s # Group by grul1 having count (gurl1)> 1) rs1 and id not in (select min (id) as id2 from % s # Group by goodsurl having count (goodsurl)> 1) rs2 "% (a, a,)

2nd: This method does not use a subset, but I don't know how to embed the tuples in rs1 and rs2 into SQL statements,

The Code is as follows:
ExeSql = "select min (id) from % s group by goodsurl havingcount (goodsurl)> 1)" % (,) Cur.exe cute (exeSql) Rs1 = cur. fetchall () ExeSql = "select goodsurl from % s group by goodsurl havingcount (goodsurl)> 1" % (,) Cur.exe cute (exeSql) Rs2 = cur. fetchall () ExeSql = "delete from % s where goodsurl in % s and id not in %" % (a, rs2, rs1) Cur.exe cute (exeSql)