Successive calculations in an update

[justin_willey]

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: create table ATest (PKID int default autoincrement, ValA int, ValB int, ValC int, primary key (PKID)); insert into ATest (ValA, ValB, ValC) values (1,0,0); insert into ATest (ValA, ValB, ValC) values (2,0,0); insert into ATest (ValA, ValB, ValC) values (3,0,0); insert into ATest (ValA, ValB, ValC) values (4,0,0); commit;

If I was doing a SELECT I could say

select ValA, ValA * 5 as ValB, ValB + 2 as ValC from ATest;

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 update ATest set ValB = ValA * 5, ValC = VacB * 2; because as the documentation says the original value of ValB (ie 0 in this example) will be used. I thought a cunning plan would be to use a variable: create variable @NewVal int; update Atest set @NewVal = ValA * 5, ValB = @NewVal, ValC = @NewVal +2; but that has the same problem. Is there any alternative to: update ATest set ValB = ValA * 5, ValC = (ValA * 5) + 2; which in my real example would involve massive repetition of the progressively longer calculations?

(Interestingly in v11 the behaviour is slightly different, the variable is calculated but uses the result from the previous row!)

v16.0.1

[VolkerBarth · Accepted solution]

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

• have automatically calculated values (say, via AUTOINCREMENT or DEFAULT TIMESTAMP defaults) that are difficult to access/calculate beforehand
• or when you need to access values for the same columns both pre- and post-UPDATE/MERGE...

[justin_willey]

Dooh - just realised I can use a derived table: update ATest set ValB = DTable.ValB, ValC = DTable.ValC from (select PKID, ValA, ValA * 5 as ValB, ValB + 2 as ValC from ATest) as DTable where Atest.PKID = DTable.PKID;