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 …