How to update table rows with values from a table join in oracle

Question

I need to update a table (table 1) with values obtained from joining table 1 with table 2

I was able to achieve this in SQL Server:

UPDATE loanacct_payment_history SET paid_by_cifno=loanacct.cifno FROM loanacct_payment_history INNER JOIN loanacct ON loanacct_payment_history.acctrefno=loanacct.acctrefno

the above SQL works perfectly in SQL Server, but doesn't run in Oracle.

I tried the following in Oracle but it's not working:

UPDATE loanacct_payment_history LAPH SET (paid_by_cifno) = (SELECT LA.cifno FROM loanacct LA WHERE LA.acctrefno = LAPH.acctrefno) WHERE EXISTS (SELECT LA.cifno FROM loanacct LA WHERE LA.acctrefno = LAPH.acctrefno)

Any help would be greatly appreciated.

Answer 1

What does "not working" mean? The following should be syntactically correct in SQL and update what you want:

UPDATE loanacct_payment_history LAPH
    SET paid_by_cifno = (SELECT LA.cifno
                         FROM loanacct LA
                         WHERE LA.acctrefno = LAPH.acctrefno
                        )
    WHERE EXISTS (SELECT LA.cifno
                  FROM loanacct LA
                  WHERE LA.acctrefno = LAPH.acctrefno
                 )