免费行业报告网站/网页制作模板

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);