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.

Perhaps this related question is helpful: http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join — Joseph B, Mar 27 '14 at 01:28
I greatly appreciate the suggestion. I'll see if I can adapt some of the code to work with multiple columns. — aNewGuy, Mar 27 '14 at 17:03

aNewGuy · Accepted Answer · 2014-03-27T19:11:03.470

0

This is what I ended up needing to do as a solution:

DECLARE
CURSOR j_CUR IS

SELECT A.[fourthCol]
FROM JJJtable A, otherTable B
WHERE A.[firstCol] = B.[firstOtherCol]
and A.[secondCol] = B.[secondOtherCol]
and A.[thirdCol] = B.[thirdOtherCol]

FOR UPDATE OF B.[fourthOtherCol];
SOME_DATE DATE;

BEGIN
FOR IDX IN j_CUR LOOP
SOME_DATE :=(IDX.[fourthCol]);
UPDATE otherTable
SET [fourthOtherCol] = SOME_DATE
WHERE CURRENT OF j_CUR;
END LOOP;
END;

Thank you for your efforts and guidance.

edited Mar 27 '14 at 19:11

answered Mar 27 '14 at 19:05

aNewGuy

11
4

I'd love to find a more elegant solution, but this one worked. – aNewGuy Mar 27 '14 at 19:07
Edited to reflect original Column nomenclature. – aNewGuy Mar 27 '14 at 19:14

score 0 · Answer 2 · answered Jan 31 '22 at 15:25

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);

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

2 Answers2