Oracle Update statement with inner join

Question

I have got an update Query which requires Inner Joins.

But I don't know how to use joins in Oracle Query

Any help would be really appreciated.

UPDATE ORDTRAN_NOM_AGG_DETAIL D
            JOIN FCS.ORDTRAN_NOM_AGG_HEADER H
                  ON H.AGG_HEADER_SEQ = D.AGG_HEADER_SEQ
        SET
             CASH_VALUE = DECODE (D.MONEY_UNITS, 'U', D.UNIT_VALUE * H.PRICE, 'M', D.CASH_VALUE),
             UNIT_VALUE = DECODE (D.MONEY_UNITS, 'U', D.UNIT_VALUE, 'M', D.CASH_VALUE * H.PRICE , D.UNIT_VALUE)
        WHERE H.AGG_HEADER_SEQ = 5379 AND D.ALLOW_PRICING = 'Y';

http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join?rq=1 — Nick, Aug 06 '14 at 15:05
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm — Gordon Linoff, Aug 06 '14 at 15:09
the "JOIN" keyword is a shorthand for "INNER JOIN" (= equality joins). You are already performing an inner join between D and H. You can add the word "INNER" before "JOIN", will give you the same results. Not sure but maybe you have to wrap "ORDTRAN_NOM_AGG_DETAIL D JOIN FCS.ORDTRAN_NOM_AGG_HEADER H ON H.AGG_HEADER_SEQ = D.AGG_HEADER_SEQ" in parenthesis - in case the SQL above is not working at all — Veverke, Aug 06 '14 at 15:52
@Veverke: you can't use `join` in an `update` statement in Oracle. Regardless if you use `inner join` or just `join`, neither works. — , May 05 '15 at 17:48

Aramillo · Answer 1 · 2015-05-05T17:29:33.117

Try something like this.

UPDATE (SELECT D.MONEY_UNITS AS MU,
               D.UNIT_VALUE AS UV,
               H.PRICE AS PR,
               D.CASH_VALUE AS CH,
               H.AGG_HEADER_SEQ AS AHS,
               D.ALLOW_PRICING AS AP
          FROM ORDTRAN_NOM_AGG_DETAIL D
               JOIN FCS.ORDTRAN_NOM_AGG_HEADER H
                  ON H.AGG_HEADER_SEQ = D.AGG_HEADER_SEQ) JN
   SET JN.CH = DECODE (JN.MU,  'U', JN.UV * JN.PR,  'M', JN.CH),
       JN.UV = DECODE (JN.MU,  'U', JN.UV,  'M', JN.CH * JN.PR,  JN.UV)
 WHERE JN.AHS = 5379 AND JN.AP = 'Y';

I hope this helps.

score 0 · Answer 2 · answered Aug 07 '14 at 01:06

The solution @Tony891206 provided is actually the ideal way to go, but it presupposes there are primary keys in place to support it. The good news is that if there are not, Oracle will complain about modifying a column for a table unsupported by the specified keys, but it won't do an incorrect update.

If his syntax does not work due to missing primary keys and you cannot add them, this syntax will work:

UPDATE ORDTRAN_NOM_AGG_DETAIL D
set (cash_value, unit_value) = (
  select
    DECODE (D.MONEY_UNITS, 'U', D.UNIT_VALUE * H.PRICE, 'M', D.CASH_VALUE),
    DECODE (D.MONEY_UNITS, 'U', D.UNIT_VALUE, 'M',
        D.CASH_VALUE * H.PRICE , D.UNIT_VALUE)
  from FCS.ORDTRAN_NOM_AGG_HEADER H
  where
    H.AGG_HEADER_SEQ = D.AGG_HEADER_SEQ and
    H.AGG_HEADER_SEQ = 5379
)
where
  D.ALLOW_PRICING = 'Y' and
  exists (
    select null
    from FCS.ORDTRAN_NOM_AGG_HEADER H
    where
      H.AGG_HEADER_SEQ = D.AGG_HEADER_SEQ and
      H.AGG_HEADER_SEQ = 5379
  )

If you are a non-Oracle person you will think I'm off my rocker, but that exists clause at the end really is necessary for this update to work. Try it without, and my guess is it will update every row in the table with the same value or something otherwise unpredictable and undesirable.

Oracle Update statement with inner join

2 Answers2