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)