Update a table from another table on multiple columns in Oracle 11g

Question

Oracle 11g SQL & both tables have the same column definitions:

VARCHAR2(11)
NUMBER
DATE
DATE

I tried to find a solution to this problem, and this is what I ended up with, which fails:

update jjjTable
set [fourthCol] = B.[fourthOtherCol]
from jjjTable, otherTable B
where jjjTable.[firstCol] = B.[firstOtherCol]
and jjjTable.[secondCol] = B.[secondOtherCol]
and jjjTable.[thirdCol] = B.[thirdOtherCol]

I'm under the impression that I need to have the from in this was based on this article:

SQL update from one Table to another based on a ID match and the edited response from Shivkant

I'm under the impression that I may need to use a join based on this article:

How do I UPDATE from a SELECT in SQL Server? and the response from Robin Day

but as I understand it, joins are only on one column match per row. I'm interested in matching on 3 elements, and I'm not finding a clear path for solution.

Any direction would be well received.

Answer 1

This is the close best I was able to get it to work on my similar use case. Try this out.

update jjjTable
SET jjjTable.[fourthCol] = (SELECT distint otherTable.fourthOtherCol from otherTable 
                            WHERE otherTable.firstOtherCol = jjjTable.firstCol and 
                                otherTable.secondOtherCol = jjjTable.secondCol and 
                                otherTable.thirdOtherCol = jjjTable.thirdCol)
WHERE EXISTS (SELECT distint otherTable.fourthOtherCol from otherTable 
                            WHERE otherTable.firstOtherCol = jjjTable.firstCol and 
                                otherTable.secondOtherCol = jjjTable.secondCol and 
                                otherTable.thirdOtherCol = jjjTable.thirdCol);