错误
在MySQL中,可能会遇到You can't specify target table '表名' for update in FROM clause这样的错误它的意思是说,不能在同一语句中,先select出同一表中的某些值,再update这个表,即不能依据某字段值做判断再来更新某字段的值。
--查询user_id为空并且 按照account_no分组account_no 大于1条的,删除记录
delete form xx_table where user_id in(
select id from xx_table where user_id is null
and account_no in(
select account_no FROM xx_table GROUP BY account_no
HAVING count(account_no)>1
)
);
解决方案
select 的结果再通过一个中间表 select 多一次,就可以避免这个错误.
delete form xx_table where user_id in(
select id from xx_table (
select id from xx_table where user_id is null
and account_no in(
select account_no FROM xx_table GROUP BY account_no
HAVING count(account_no)>1
)
)A
);