mysql error 1093 解决方法

mysql> select * from t;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+----+
10 rows in set (0.00 sec)

mysql> delete from t where id in (select id from t where id < 5);
ERROR 1093 (HY000): You can't specify target table 't' for update in FROM clause
mysql> 

这样删除将报错，更改SQL语句为

mysql> delete  from t where id in (select * from (select id from t where id < 5) tmp);
Query OK, 4 rows affected (0.00 sec)

以这样的形式即可删除。

再优化之，改为表连接模式：

mysql> delete t from t join (select id from t where id < 5) tmp on t.id=tmp.id;
Query OK, 4 rows affected (0.01 sec)

转自 http://hcymysql.blog.51cto.com/5223301/895517