CODEWITHSUNDEEP
sqloracle-databasesql-update
Sigma1
Sigma1

Reputation: 19

Oracle Update statement with inner join

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

Upvotes: 0

Views: 311

Answers (2)

Aramillo
Aramillo

Reputation: 3216

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.

Upvotes: 1

Hambone
Hambone

Reputation: 16377

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.

Upvotes: 0

Related Questions