MySQL执行更新语句报错:
更新语句:UPDATE test SET state=50 WHERE num IN(SELECT num FROM test WHERE state=60);
报错:ERROR 1093 (HY000): You can't specify target table 'test' for update in FROM clause
表数据:
state num
50 101
50 102
50 201
60 202
60 203
解决方式:同表不支持 update子查询结果,将子查询结果,再select一次,就可以了
改成:UPDATE test SET state=50 WHERE num IN(SELECT * FROM (SELECT num FROM test WHERE state=60));