I have a problem where I am trying to use the result of a calculation, in a further calculation in an UPDATE statement.
This is a reporting situation where the results of fairly complex calculations (which could change at a future time - and therefore can't just be recalculated on demand) are being recorded in a table. A vastly simplified example:
If I was doing a SELECT I could say
ValA ValB ValC ==== ==== ==== 1 5 7 2 10 12 3 15 17 4 20 22
However I want to write ValB & ValC into the table. I can't do
(Interestingly in v11 the behaviour is slightly different, the variable is calculated but uses the result from the previous row!)
asked 23 Jan, 11:36
I guess you could also use a DML-derived table, although I would think in your case the derived table is easier to use.
Here's a sample - leading to the same result as your sample but doing a two-step UPDATE within one statement:
update ATest set ValC = ATest_Upd.ValB + 2 from (update ATest set ValB = ValA * 5) referencing (final as ATest_Upd) where Atest.PKID = ATest_Upd.PKID;
I guess those DML-derived tables are particularly helpful if you
Dooh - just realised I can use a derived table:
answered 23 Jan, 11:56