you can't specify target table for update in from

原创
2016/07/29 17:09
阅读数 43

asset表里ip相同的字段有多个,现在要完成一个功能是,根据一个ip,获得任意一个id,当asset_extension表里department=0时,才根据该id更新asset_extension表里 的department为1:

update asset_extension t1 set t1.department=4 where t1.asset_id=(select t2.id from asset t2 join asset_extension t1 on t2.id=t1.asset_id where t1.department=0 and t2.ip='192.168.58.8' group by t2.ip)

结果运行时报错:you can't specify  target table 't1' for update in from clause.

改成下面这样即可,即中间增加一个过渡,不要直接在t1表时同时查询和更新:

update asset_extension t1 set t1.department=4 where t1.asset_id=(select * from(select t2.id from asset t2 join asset_extension t1 on t2.id=t1.asset_id where t1.department=0 and t2.ip='192.168.58.8' group by t2.ip) as subtable);

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部