1.[Err] 1093 - You can’t specify target table ‘iot_device’ for update in FROM clause
这sql看上去没问题,但是在Mysql中会报错;在其他数据库可能不会出错
update iot_device set name='xxx' where id in (select id from iot_device where orgId='600205' );
解决这个问题就要用到虚表(就是再套一层 select );
把sql改成这样就可以了:
update iot_device set name='xxx' where id in (select t.id from (select id from iot_device where orgId='600205') t);
2.批量更新成其他字段
update shuangkua_hangye hy
left join sheet1 s ON hy.id=s.device_num
left join iot_area a ON a.area_name=s.device_eara
set hy.device_area_id=a.id
where hy.is_delete=0;