1、首先用查出来要处理的sql，准备更新用

-- 查询承租人对应的all表的guid 
select c.guid as zf_guid,al.guid as guid_all from (
select i.id,o.guid, o.renter_id as guid_info,i.orgid,info.idcard as idcard_info

from t_rental_house_oper_info_20200323 o
left join t_rental_house_info_20200323 i on o.rental_houseid=i.id
left join building_resident_info info on o.renter_id=info.guid
where o.status='1' and i.status='1' and o.renter_id is not null and info.idcard is not null
) c
left join building_resident_all al on c.orgid=al.orgid and c.idcard_info=al.idcard

效果：

 

 

 

 

2、然后，更新语句 把上面的sql结果集，当作一张表来用，我这别名为 v 的，是上面的结果集，后面再写set和where条件

-- 更新的时候把结果集当作一张表来用
update t_rental_house_oper_info_20200323 a ,(
select c.guid as zf_guid,al.guid as guid_all from (
select i.id,o.guid, o.renter_id as guid_info,i.orgid,info.idcard as idcard_info

    from t_rental_house_oper_info_20200323 o
    left join t_rental_house_info_20200323 i on o.rental_houseid=i.id
    left join building_resident_info info on o.renter_id=info.guid
where o.status='1' and i.status='1' and o.renter_id is not null and info.idcard is not null
) c
left join building_resident_all al on c.orgid=al.orgid and c.idcard_info=al.idcard
) v
set a.guid_all=v.guid_all where a.guid=v.zf_guid

效果：