1、两表(多表)关联update -- 被修改值由另一个表运算而来
update customers a
set city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)
where exists (select 1
from tmp_cust_city b
where b.customer_id=a.customer_id)
实例:
update bd_psndoc
set bd_psndoc.indutydate=(select begindate from (select a.pk_psnbasdoc as pk_psnbasdoc,
max(b.begindate) as begindate
from temp_psnbasdoc a left join hi_psndoc_deptchg b
on a.pk_psnbasdoc=b.pk_psnbasdoc and a.recordnum=b.recordnum
group by a.pk_psnbasdoc) tt where tt.pk_psnbasdoc=bd_psndoc.pk_psnbasdoc)
where exists(select 1 from (select a.pk_psnbasdoc as pk_psnbasdoc,
max(b.begindate) as begindate
from temp_psnbasdoc a left join hi_psndoc_deptchg b
on a.pk_psnbasdoc=b.pk_psnbasdoc and a.recordnum=b.recordnum
group by a.pk_psnbasdoc) tt where tt.pk_psnbasdoc=bd_psndoc.pk_psnbasdoc)
and pk_psncl='00015T100000000003M2' and indutydate is null