有没有一种方法可以使用merge语句将此查询转换为Oracle查询

Question

Update
vtnhmashaupl as UPL 
inner join
vtsmvaccounts vacc 
on vaccines. Vaiban = upl.vaiban 
Inner join
vtsmvaccfmrmap as vfmr 
on vacc.vaibanid = vfmr.vaibanid 
and vfmr.fmrcode = upl.fmrcode 
inner join
vtnhmbudget bd 
on bd.hloccode = vacc.vacustidnt 
inner join
vtnhmbudgetdtls dtls 
on dtls.fmrcode = upl.fmrcode 
and ifnull (Dtls.fnyear, bd.fnyear) = in_fnyear 
set
upl.buddtlsid = dtls.buddtlsid, upl.budid = dtls.budid, upl.hloccode = bd.hloccode;

Answer 1

我认为这符合您的期望。没有表格结构和样本输入数据，我不可能测试它

merge into vtnhmashaupl upl
using ( select vacc.vaiban
, vfmr.fmrcode
, dtls.buddtlsid
, dtls.budid
, bd.hloccode
from vtsmvaccounts vacc 
inner join vtsmvaccfmrmap as vfmr 
on vacc.vaibanid = vfmr.vaibanid 
inner join vtnhmbudget bd 
on bd.hloccode = vacc.vacustidnt 
inner join vtnhmbudgetdtls dtls 
on dtls.fmrcode = vfmr.fmrcode
and nvl (dtls.fnyear, bd.fnyear) = in_fnyear 
) sq
on ( sq.vaiban  = upl.vaiban  
and sq.fmrcode = upl.fmrcode )         
when matched then 
update          
set upl.buddtlsid = sq.buddtlsid
, upl.budid = sq.budid
, upl.hloccode = sq.hloccode;