Oracle UPDATE with JOIN 3 tables?

Question

UPDATE calculation  
LEFT JOIN transportorders ON calculation.ataid = transportorders.nr
LEFT JOIN partner ON partner.taid = transportorders.nr 
      AND calculation.FZZAEHLER=ATAFRACHTZAHLER.FZZAEHLER
SET calculation.BABRECHNUNGSINFO = '.....' 
WHERE calculation.ataid IN
(SELECT transportorders.nr 
 FROM transportorders 
 WHERE transportorders.tournr = '212041') 
AND partner.kundenid = '2486909';

I am trying this code, but it gives me an error that SET keyword is not found. Which means the joins are not as they should be. How do I make this selection in UPDATE? Regular SELECT works fine with the same tables.

Littlefoot · Accepted Answer · 2022-03-28T07:54:21.957

0

Try merge:

MERGE INTO calculation c
     USING (SELECT '....' new_value, t.nr
              FROM transportoders t LEFT JOIN partner p ON p.taid = t.nr
             WHERE     t.tournr = '212041'
                   AND p.kundenid = '2486909') x
        ON (c.ataid = x.nr)
WHEN MATCHED
THEN
   UPDATE SET c.babrechnungsinfo = x.new_value;

edited Mar 28 '22 at 07:54

answered Mar 28 '22 at 07:36

Littlefoot

131,892
15
35
57

It selects nothing. 0 rows updated, when it should be 2. And I cannot understand why. – DarkBlade Mar 28 '22 at 07:53
There was a typo in TOURNR value; how about now? – Littlefoot Mar 28 '22 at 07:54
I found another solution. In the WHERE clause with IN. Anyway, I will test your solution as well later. – DarkBlade Mar 29 '22 at 08:34
It merges all 6 rows instead of the 4 needed. – DarkBlade Mar 29 '22 at 11:08

Oracle UPDATE with JOIN 3 tables?

1 Answers1