Re: UPDATE of several columns using SELECT statement

["adam.slachta" <adam.slachta@xxxxxxxxx>]

Well, I should have been more specific and not use such a simplified example, which only lead you into wrong  direction. What I am really tried to solve instead of

 

UPDATE myFirstTable SET (fistCol, secCol) = (  SELECT anotherFistCol, anotherSecondCol FROM mySecondTable )

 

is this:

 

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

UPDATE limit_breach lb  

        SET (max_breach, limit_value) =  

            ((  

                SELECT ABS(ov.outright_volume) - NVL(ov.hedge_limit,0), hedge_limit   

                FROM   

                (   

                outrightvolume_breach 

                )  

                ov  

                WHERE ov.hedging_desk=lb.hedging_desk  

                  AND ov.idmarket = lb.idmarket  

                  AND ov.symbol = lb.symbol  

                  AND ov.limit_name = lb.limit_name  

                  AND lb.breach_end IS NULL  

            ))    

        WHERE  

          lb.breach_end IS NULL  

         AND  

         (lb.hedging_desk, lb.idmarket, lb.symbol, lb.limit_name)  

          IN  

           (  

              SELECT hedging_desk, idmarket, symbol, limit_name  

              FROM  

              (  

                       outrightvolume_breach 

              ) ov  

              WHERE (ABS(ov.outright_volume) - NVL(ov.hedge_limit,0)) > lb.max_breach  

           )  ;

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Do you think there might be a way how to solve this not leading into splitting the update into two separate update statements for each of the two columns (max_breach, limit_value) ?

 

Adam