Just wondering how can i convert this below query to a oracle query using merge statement?

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

With what you posted so far, could be something like this (presuming that last line’s IN_WFID represents some kind of a parameter):

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;