mysql delete语句问题

表结构如下：

mysql> desc source_user_data;
+--------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(255) | YES | | NULL | |
| mobile_phone | varchar(30) | YES | | NULL | |
| email | varchar(60) | NO | MUL | NULL | |
| province | varchar(50) | YES | | NULL | |
| city | varchar(50) | YES | | NULL | |
| post | varchar(50) | YES | | NULL | |
| is_jianli | double | YES | | NULL | |
+--------------+------------------+------+-----+---------+----------------+

使用delete删出表数据的时候，使用如下语句报错：

mysql> DELETE FROM `source_user_data`
    -> WHERE email IN (SELECT email FROM source_user_data GROUP BY email HAVING COUNT(*) > 1)
    -> AND id NOT IN (SELECT MIN(id) FROM source_user_data GROUP BY email HAVING COUNT(*)>1);
ERROR 1093 (HY000): You can't specify target table 'source_user_data

文档中说：

 

 

In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:

DELETE FROM t WHERE ... (SELECT ... FROM t ...);UPDATE t ... WHERE col = (SELECT ... FROM t ...);{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the

FROM

clause. Example:

UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);

Here the prohibition does not apply because a subquery in the

FROM

clause is materialized as a temporary table, so the relevant rows in

t

have already been selected by the time the update to

t

大概就是说要把子查询当成个临时表启个别名再套一层。

好吧，修改成以下的SQL就通过了：

mysql> DELETE FROM source_user_data WHERE email IN (SELECT a.email FROM (SELECT b.* FROM source_user_data b GROUP BY b.email HAVING COUNT(b.email) > 1) a)
    -> AND id NOT IN (SELECT d.id FROM (SELECT MIN(c.id) AS id FROM source_user_data c GROUP BY email HAVING COUNT(email)>1) d);
Query OK, 1 row affected (0.12 sec)