March 4, 2009 at 1:41 am
Hello,
I've trying to write an update statement that updates multiple columns in a table. One of those columns (VAL2) should contain a calculated value of another column (VAL1) in the same table, however that column also gets updated in the same statement. The calculated value of VAL2 does not use the new value of VAL1.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST]') and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table [dbo].[TEST]
GO
CREATE TABLE TEST
(
val1 [int],
val2 [int])
GO
insert into TEST values (100,0)
update TEST
set
val1=val1/2,
val2=val1*2
The result is:
val1 val2
50200
where I expect to have 50 100.
I would like to avoid doing something like
update TEST
set
val1=val1/2,
val2=(val1/2)*2
because the calculations can become quite complex and I don't want to do them twice.
Any suggestions?
March 4, 2009 at 1:52 am
That won't work, as both updates are getting executed at the same time, run them after one after another or try something like this--
do simple select first
select val1=val1/2,
val2=(val1/2)*2
from test
EDIT : Oops I din't notice this part in hurry
I would like to avoid doing something like
update TEST
set
val1=val1/2,
val2=(val1/2)*2
because the calculations can become quite complex and I don't want to do them twice.
Any suggestions?
John Smith
March 5, 2009 at 12:29 pm
You could provide a local variable to hold the intermediate result, then use it to set val1 and val2.
Declare @newVal1 int
update TEST
set @newVal1=val1/2,
val1=@newVal1,
val2=@newVal1 * 2
select * from Test
March 5, 2009 at 11:48 pm
thanks for your replies.
I think I might go for the local variable solution.
March 6, 2009 at 12:29 am
I don't understand your requeriments very well, but in order to simplify the process, i think you don't need any local variable; only use the original value of the column.
update TEST
set val1=val1/2, val2=val1
And you get:
val1 val2
----------- -----------
50 100
You don't need nor variable nor more operations. val1 has the original value in all cases, isn't it?
March 6, 2009 at 10:48 am
serinor.e090266 (3/6/2009)
I don't understand your requeriments very well, but in order to simplify the process, i think you don't need any local variable; only use the original value of the column.. . . .
You don't need nor variable nor more operations. val1 has the original value in all cases, isn't it?
Yes, val1 has the original value and that's the problem. The OP wants to change multiple columns in a single update with the calculations of some columns (like val2) based on the newly determined values of other columns. Since each SET clause will see column values as they are in the db, you need a way to make the intermediate result available.
March 9, 2009 at 3:19 am
Thanks for your explanation john.
Now i have understood the question, and i think the better solution is a local variable in middle of the UPDATE.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply