mysql delete 注意

mysql中You can't specify target table <tbl> for update in FROM clause错误的意思是说，不能先select出同一表中的某些值，再update这个表(在同一语句中)。

例如下面这个sql： 报错

?

1 2 3 4 5 6

DELETE from monthxl  where dateTime in (     SELECT a.dateTime  from monthxl a  where a.dateTime !=(         select max(b.dateTime) from monthxl b where a.month=b.month     ) )

修改如下：

?

1 2 3 4 5 6 7

DELETE FROM monthxl where dateTime in <br>(     select b.dateTime from ( -- 用临时表 包装一层 再删除         SELECT a.month,a.dateTime  from monthxl a  where a.dateTime !=(             select max(b.dateTime) from monthxl b where a.month=b.month         )          ) b )