# Successive calculations in an update

 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 asked 23 Jan '17, 11:36 Justin Willey 7.0k●117●150●221 accept rate: 21%

 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... answered 23 Jan '17, 15:43 Volker Barth 31.6k●321●465●678 accept rate: 32%
 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; ``` answered 23 Jan '17, 11:56 Justin Willey 7.0k●117●150●221 accept rate: 21%
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

×212
×33

question asked: 23 Jan '17, 11:36

question was seen: 389 times

last updated: 25 Jan '17, 07:36