执行以下语句:
delete
from titles_test
where id not in
(
select min(id)
from titles_test
group by emp_no
);
会报出错误:
[1093] You can't specify target table 'titles_test' for update in FROM clause
原因:
MySQL的update或delete中的子查询不能是同一张表。即不能先用select查出同一张表的某些值,再用update或delete操作同一张表。
解决方法:
将子查询的表添加个别名即可。
delete
from titles_test
where id not in
(
select *
from (
select min(id)
from titles_test
group by emp_no) temp
);