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
and get 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!) v16.0.1 asked 23 Jan '17, 11:36 Justin Willey |
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
answered 23 Jan '17, 15:43 Volker Barth |
Dooh - just realised I can use a derived table:
answered 23 Jan '17, 11:56 Justin Willey |