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, 11:36

Justin%20Willey's gravatar image

Justin Willey
6.5k104135200
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...
permanent link

answered 23 Jan, 15:43

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

edited 25 Jan, 07:36

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;

permanent link

answered 23 Jan, 11:56

Justin%20Willey's gravatar image

Justin Willey
6.5k104135200
accept rate: 21%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • 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:

×190
×27

question asked: 23 Jan, 11:36

question was seen: 106 times

last updated: 25 Jan, 07:36