只是想知道如何使用merge语句将下面的查询转换为oracle查询

Question

UPDATE WRK_VTNHMBENTEMP BEN
INNER JOIN wrk_vtnhmbenaccmap ACC ON BEN.WFID = ACC.WFID
LEFT OUTER JOIN vtsmbankbranch BNK ON BNK.BANKNAME = ACC.benbankname
BEN.CURRSTATUSDESC := NVL(BEN.CURRSTATUSDESC,'')||'|'||'Bank Name is not Valid: '||ACC.benbankname;Ben.CURRSTATUSCODE:='30'
where BEN.WFID=IN_WFID AND BNK.BANKBRANCHID IS NULL
;

Answer 1

到目前为止，您发布的内容可能是这样的(假设最后一行的IN_WFID代表某种参数(：

MERGE INTO wrk_vtnhmbentemp ben
USING (SELECT acc.wfid, acc.benbankname
FROM wrk_vtnhmbenaccmap acc
LEFT JOIN vtsmbankbranch bnk
ON bnk.bankname = acc.benbankname
WHERE bnk.bankbranchid IS NULL) x
ON (ben.wfid = x.wfid)
WHEN MATCHED
THEN
UPDATE SET
ben.currstatusdesc =
NVL (ben.currstatusdesc, ' ')
|| '|'
|| 'Bank name is not valid: '
|| x.benbankname,
ben.currstatuscode = '30'
WHERE ben.wfid = in_wfid;