mysql update 使用in_mysql update in 报错解决

在用到mysql  update in的时候报错

update area set status=‘ON‘ where id in(select id from area where status=‘ON‘ and name like ‘%市辖%‘)

[SQL]update area set status=‘ON‘ where id in(select id from area where status=‘ON‘ and name like ‘%市辖%‘)

[Err] 1093 - You can‘t specify target table ‘area‘ for update in FROM clause

下面语句可以通过

update area a inner join(select id from area where id in(select id from area where status='ON' and name like '%市辖%')) b on a.id=b.id set a.status='OFF'

select * from area a inner join(select id from area where id in(select id from area where status='ON' and name like '%市辖%')) b on a.id=b.id

原文：http://blog.csdn.net/liangrui1988/article/details/43984907