August 1, 2011 at 8:31 am
Hi,
I know multiple columns can be updated in a single UPDATE statement like this:
UPDATE t SET field1 = expr1, field2 = expr2
Is there a guarantee that expr1 will be evaluated and assigned to field1 BEFORE expr2 is evaluated and assigned to field2?
I would like to write something like this:
UPDATE t SET field1 = CASE field2 > 7 THEN expr11 ELSE expr12 END, field2 = expr2
The value I would like to assign to field1 should depend on the current value of field2 (i.e., before it is assigned expr2).
Hope this question is clear.
Thanks,
Jamie
August 1, 2011 at 8:38 am
You can't do that - all of your field references will be to the situation as it was 'before update'.
So you need to write things out in full rather than using your cascading logic.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 1, 2011 at 8:40 am
Phil Parkin (8/1/2011)
You can't do that - all of your field references will be to the situation as it was 'before update'.So you need to write things out in full rather than using your cascading logic.
Are you 100% sure about that one ;-).
DECLARE @demo TABLE (a int, b int)
INSERT INTO @demo (
a,
b
) VALUES ( 0, 1)
SELECT * FROM @demo
UPDATE @demo SET a = b, b = a
SELECT * FROM @demo
a b
----------- -----------
0 1
a b
----------- -----------
1 0
August 1, 2011 at 8:40 am
If all field values contain their original value (i.e., before the update), then that's exactly what I wanted to know. This is the guarantee I was looking for. Thank you!
August 1, 2011 at 8:43 am
Jamie Julius (8/1/2011)
If all field values contain their original value (i.e., before the update), then that's exactly what I wanted to know. This is the guarantee I was looking for. Thank you!
Just to make sure you read my previous message....
August 1, 2011 at 8:51 am
Your example also demonstrates that the fields hold their original values. Thanks. I just didn't know if this is something, which SQL guarantees or is implementation-dependent.
August 1, 2011 at 8:58 am
I'd still like a bol refference on this one. The optimizer is a tricky beast.
Anyone has a link to says that the set statement is ALWAYS eveluated / executed from left to right?
August 1, 2011 at 9:00 am
Are you 100% sure about that one [Wink].
I know you've been around here for a very long time and I hate to suggest that you are wrong, but how does your example contradict my post?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 1, 2011 at 9:01 am
I think it does it always, otherwise "running total update" method wouldn't work.
August 1, 2011 at 9:03 am
Ninja's_RGR'us (8/1/2011)
I'd still like a bol refference on this one. The optimizer is a tricky beast.Anyone has a link to says that the set statement is ALWAYS eveluated / executed from left to right?
That's not the point here.
All that my post was intended to assert was that field references within an UPDATE statement reflect their before-UPDATE values.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 1, 2011 at 9:06 am
Phil Parkin (8/1/2011)
Are you 100% sure about that one [Wink].
I know you've been around here for a very long time and I hate to suggest that you are wrong, but how does your example contradict my post?
Please do point out errors. As this one clearly was in my assumptions.
As previously said it pretty much proves your point instead of the contrary.
August 1, 2011 at 9:07 am
Phil Parkin (8/1/2011)
Ninja's_RGR'us (8/1/2011)
I'd still like a bol refference on this one. The optimizer is a tricky beast.Anyone has a link to says that the set statement is ALWAYS eveluated / executed from left to right?
That's not the point here.
All that my post was intended to assert was that field references within an UPDATE statement reflect their before-UPDATE values.
I was thinking out loud. That's what it would take for me to call that as a rule rather than it seems to work that way. Maybe Jeff has more info on this!
August 1, 2011 at 9:22 am
A nice long discussion about this exact topic. Doesn't seem to have anything concluding tho :
http://www.sqlservercentral.com/Forums/Topic1040684-2785-1.aspx
August 1, 2011 at 10:02 am
Ninja's_RGR'us (8/1/2011)
A nice long discussion about this exact topic. Doesn't seem to have anything concluding tho :http://www.sqlservercentral.com/Forums/Topic1040684-2785-1.aspx
As long as you're using bound columns within your update and not using double assignments I think you're in the clear. That said - the infamous "quirky update" used for running totals, etc... does seem to to show that the values end up being set in the order you put them in.
create table #test1 (rid int, increment int, increment2 int)
go
insert #test1 (rid)
values(1)
go 50
declare @fun int
declare @fun1 int
set @fun=0
set @fun1=0
update #test1
set @fun=increment=@fun1+4,
rid=rid+@fun,
--increment=@fun,
@fun1=@fun+1,
increment2=@fun1
select * from#test1 order by increment
So - it's a bit more complicated, but still seems to line up with the initial explanation.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply