Mysql常见问题

You can’t specify target table ‘xxx’ for update in FROM clause

Mysql中，在对某一张表delete或update时，如果from里面是个对同一张表的查询子语句，会报该错：You can’t specify target table ‘xxx’ for update in FROM clause。

如表去重：

DELETE from user where id not in (select max(id) as mid from user group by userId);

解决办法：在from中的子查询外面，再嵌套一层，作为临时表。如下：

DELETE from user where id not in (SELECT mid from (select max(id) as mid from user group by userId) b);

另外，左连接方式是可以的，但与正常的delete语句稍稍写法不同：

delete user from user left join (select min(id) as mid from user group by userId) b on id=mid where mid is null;

注意此处delete user from …