UPDATE payment_data rr SET rr.penalty_date = '2017-4-12' where rr.id = (SELECT min(r.id) FROM payment_data r where data_no = (SELECT data_no FROM user_debt WHERE out_trade_no = 'bestpay_order_no1491812746329'));
ERROR 1093 (HY000): You can't specify target table 'rr' for update in FROM clause 如果對MySQL查詢優化器足夠了解就會明白，其實這種方式是MySQL不支持的，有沒有WA呢，還是有的，那就是通過一種特殊的子查詢來完成，也就是derived table
所以上面的語句使用如下的方式就可以破解。
UPDATE payment_data rr SET rr.penalty_date = '2017-4-12' where rr.id = (SELECT min(t.id) FROM (select id,data_no from payment_data r) t where t.data_no = (SELECT data_no FROM user_debt WHERE out_trade_no = 'bestpay_order_no1491812746329')); 我們回到剛剛提到的Derived table,在官方文檔中是這么說的。
第一種：
> select * from (select id from t_fund_info) t where t.id=138031; 1 row in set (1.12 sec)這個時候查看執行計劃，就會看到derived table的字樣。
> explain select * from (select id from t_fund_info) t where t.id=138031; +----+-------------+-------------+-------+---------------+---------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+---------+---------+------+---------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1998067 | Using where | | 2 | DERIVED | t_fund_info | index | NULL | account | 182 | NULL | 2127101 | Using index | +----+-------------+-------------+-------+---------------+---------+---------+------+---------+-------------+ 2 rows in set (0.90 sec)看起來是1秒的執行速度，差別還不是很大，我們換第二種方式。
> select * from (select * from t_fund_info) t where t.id=138031; ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_3e34_0.MYI'; try to repair it 這個時候就會發現這么一個看似簡單的查詢竟然拋出了錯誤。
