1. 报错信息:
Error Code: 1093. You can't specify target table 'self_check' for update in FROM clause
错误的sql语句:
update self_check set calculated=1 where self_check.id in (select distinct self_check.id from self_check, self_result where self_check.selfId=self_result.selfId and self_check.chainId=self_result.chainId order by self_check.id)
错误原因:
不能根据某个表select的字段去update这个表(对同一个表的where和update不能出现在同一语句中)
可以再套一层select,即:update self_check set calculated=1 where self_check.id in (select * from (select distinct self_check.id from self_check, self_result where self_check.selfId=self_result.selfId and self_check.chainId=self_result.chainId order by self_check.id))
参考文章:https://blog.csdn.net/chinafire525/article/details/91045069
2. 改成上述sql后出现了新的问题
报错信息:
Error Code: 1248. Every derived table must have its own alias
错误原因:
对于查询得到的派生表要起个别名
将sql改成:update self_check set calculated=1 where self_check.id in (select * from (select distinct self_check.id from self_check, self_result where self_check.selfId=self_result.selfId and self_check.chainId=self_result.chainId order by self_check.id) as checkIds)
3. 改成上述sql后又出现了新问题
报错信息:
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
错误原因:
为了防止没有设置where就update所有数据(防止update误操作),workbench默认会设置为safe update模式。错误信息说的是在update table的时候where没有限定主键,使得update不能根据主键来更新,存在一定的问题。
其实我这个self_check.id是self_check表的主键,但是可能因为它后续接着的sql语句是select *,导致mysql判断为没有对主键做限定。这种情况按照上面的错误信息把safe update模式关闭即可:File->Preferences->SQL Editor->Safe Updates取消勾选->OK
4. 终于成功执行了,可喜可贺*★,°*:.☆( ̄▽ ̄)/$:*.°★*