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
