explanation of update statement

--begin script
if exists (select * from information_schema.tables where table_name = 't1')
drop table t1
go
create table t1(c1 int, c2 int)
insert into t1(c1) values(1)
insert into t1(c1) values(2)
insert into t1(c1) values(3)
insert into t1(c1) values(4)
go
declare @.a int
set @.a = 1
update t1 set @.a = c2 = 2*@.a
go
select * from t1
-- end script
--begin output
c1 c2
-- --
1 2
2 4
3 8
4 16
--end output
The above script works very well, I was not expecting it to work, specially
the statement
update t1 set @.a = c2 = 2*@.a
I thought we always need a cursor for row by row processing, but this update
statement seems to be looping through the rows just like a cusrsor.
I just wanted to know if this is recomended way of avoiding a cursor or if
there are any known pitfalls to this approach.
Thanks in advance
--
Vikram Vamshi
Database Engineer
Eclipsys CorporationAnother way and probably safer (won't break with a service pack for
example)
update t1 set c2 = power(2,c1)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Vikram Vamshi wrote:
> --begin script
> if exists (select * from information_schema.tables where table_name = 't1'
)
> drop table t1
> go
> create table t1(c1 int, c2 int)
> insert into t1(c1) values(1)
> insert into t1(c1) values(2)
> insert into t1(c1) values(3)
> insert into t1(c1) values(4)
> go
> declare @.a int
> set @.a = 1
> update t1 set @.a = c2 = 2*@.a
> go
> select * from t1
> -- end script
> --begin output
> c1 c2
> -- --
> 1 2
> 2 4
> 3 8
> 4 16
> --end output
> The above script works very well, I was not expecting it to work, speciall
y
> the statement
> update t1 set @.a = c2 = 2*@.a
> I thought we always need a cursor for row by row processing, but this upda
te
> statement seems to be looping through the rows just like a cusrsor.
> I just wanted to know if this is recomended way of avoiding a cursor or if
> there are any known pitfalls to this approach.
> Thanks in advance
> --
> Vikram Vamshi
> Database Engineer
> Eclipsys Corporation|||Be sure to follow SQL Menace's point about removing the @.a reference.

>I thought we always need a cursor for row by row processing, but this updat
e
>statement seems to be looping through the rows just like a cusrsor.
What you are seeing is SET processing. SET based processing is
exactly what SQL is all about. The entire set of rows that meet the
WHERE clause criteria - which in this case is all rows, since there is
no WHERE clause - is updated.

>I just wanted to know if this is recomended way of avoiding a cursor
Yes, SET based processing is the preferred alternative to cursors.

>there are any known pitfalls to this approach.
Make sure you write your WHERE clause correctly! If you forget it, or
get it wrong, an UPDATE against the wrong set of rows can ruin your
whole day.
Roy Harvey
Beacon Falls, CT
On Tue, 27 Jun 2006 12:35:47 -0400, "Vikram Vamshi"
<vikram.vamshi@.online.eclipsys.com> wrote:

>--begin script
>if exists (select * from information_schema.tables where table_name = 't1')
> drop table t1
>go
>create table t1(c1 int, c2 int)
>insert into t1(c1) values(1)
>insert into t1(c1) values(2)
>insert into t1(c1) values(3)
>insert into t1(c1) values(4)
>go
>declare @.a int
>set @.a = 1
>update t1 set @.a = c2 = 2*@.a
>go
>select * from t1
>-- end script
>--begin output
>c1 c2
>-- --
>1 2
>2 4
>3 8
>4 16
>--end output
>The above script works very well, I was not expecting it to work, specially
>the statement
>update t1 set @.a = c2 = 2*@.a
>I thought we always need a cursor for row by row processing, but this updat
e
>statement seems to be looping through the rows just like a cusrsor.
>I just wanted to know if this is recomended way of avoiding a cursor or if
>there are any known pitfalls to this approach.
>Thanks in advance|||I think my last example left some ambiguity...
--begin script
if exists (select * from information_schema.tables where table_name = 't1')
drop table t1
go
create table t1(c1 char, c2 int)
insert into t1(c1) values('a')
insert into t1(c1) values('x')
insert into t1(c1) values('n')
insert into t1(c1) values('e')
go
declare @.a int
set @.a = 1
update t1 set @.a = c2 = 2*@.a
go
select * from t1
-- end script
There is no relation between c1 and c2 ( i modified the sample to reflect
that )
The update statement seems to be using the previous value it has inserted
for c2 to generate the next value.
I am still not sure if it will work in all the cases, but in all my tests it
has worked without any issues.
Is it always gauranteed to work this way? Or is it safer to use a cursor for
this?
Thanks for taking your time to respond
--
Vikram Vamshi
Database Engineer
Eclipsys Corporation
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:gsp2a2h7mbadq7c8eo260j5d359t9pj29a@.
4ax.com...
> Be sure to follow SQL Menace's point about removing the @.a reference.
>
> What you are seeing is SET processing. SET based processing is
> exactly what SQL is all about. The entire set of rows that meet the
> WHERE clause criteria - which in this case is all rows, since there is
> no WHERE clause - is updated.
>
> Yes, SET based processing is the preferred alternative to cursors.
>
> Make sure you write your WHERE clause correctly! If you forget it, or
> get it wrong, an UPDATE against the wrong set of rows can ruin your
> whole day.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 27 Jun 2006 12:35:47 -0400, "Vikram Vamshi"
> <vikram.vamshi@.online.eclipsys.com> wrote:
>|||What you're getting is the following:
@.a = 1
SET @.a = c2 = 2*1 -- so @.a becomes 2
SET @.a = c2 = 2*2 -- so @.a becomes 4
SET @.a = c2 = 2*4 -- so @.a becomes 8
etc.
It is documented in BOL:
"SET @.variable = column = expression sets the variable to the same value as
the column. This differs from SET @.variable = column, column = expression,
which sets the variable to the pre-update value of the column."
So one would assume it is safe for use.
"Vikram Vamshi" <vikram.vamshi@.online.eclipsys.com> wrote in message
news:%23EMjlChmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>I think my last example left some ambiguity...
> --begin script
> if exists (select * from information_schema.tables where table_name =
> 't1')
> drop table t1
> go
> create table t1(c1 char, c2 int)
> insert into t1(c1) values('a')
> insert into t1(c1) values('x')
> insert into t1(c1) values('n')
> insert into t1(c1) values('e')
> go
> declare @.a int
> set @.a = 1
> update t1 set @.a = c2 = 2*@.a
> go
> select * from t1
> -- end script
> There is no relation between c1 and c2 ( i modified the sample to reflect
> that )
> The update statement seems to be using the previous value it has inserted
> for c2 to generate the next value.
> I am still not sure if it will work in all the cases, but in all my tests
> it has worked without any issues.
> Is it always gauranteed to work this way? Or is it safer to use a cursor
> for this?
> Thanks for taking your time to respond
> --
> Vikram Vamshi
> Database Engineer
> Eclipsys Corporation
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:gsp2a2h7mbadq7c8eo260j5d359t9pj29a@.
4ax.com...
>|||Thanks Mike!!! Not sure how I missed it in the books online :)
What about the implicit looping, is it reliable?
For now we will go ahead and use it, If we run into any issues, I will post
them here ...
Thanks again
--
Vikram Vamshi
Database Engineer
Eclipsys Corporation
"Mike C#" <xyz@.xyz.com> wrote in message
news:eyGE7zhmGHA.3352@.TK2MSFTNGP02.phx.gbl...
> What you're getting is the following:
> @.a = 1
> SET @.a = c2 = 2*1 -- so @.a becomes 2
> SET @.a = c2 = 2*2 -- so @.a becomes 4
> SET @.a = c2 = 2*4 -- so @.a becomes 8
> etc.
> It is documented in BOL:
> "SET @.variable = column = expression sets the variable to the same value
> as the column. This differs from SET @.variable = column, column =
> expression, which sets the variable to the pre-update value of the
> column."
> So one would assume it is safe for use.
> "Vikram Vamshi" <vikram.vamshi@.online.eclipsys.com> wrote in message
> news:%23EMjlChmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>|||This might cause you potential problems with some future release, SP, or
even just changes in your data. It is true that you can do
@.variable = column = expression, and that updates the variable to the new
value in the column. But Vikram is updating multiple rows, and the
optimizer is allowed to update these rows in any order. If you are
depending on the first row getting 2, the second row getting 4, etc, it
won't work if the rows are updated in some other order.
As BOL also says, "Variable names can be used in UPDATE statements to show
the old and new values affected. This should only be used when the UPDATE
statement affects a single record; if the UPDATE statement affects multiple
records, the variables only contain the values for one of the updated rows."
Tom
"Mike C#" <xyz@.xyz.com> wrote in message
news:eyGE7zhmGHA.3352@.TK2MSFTNGP02.phx.gbl...
> What you're getting is the following:
> @.a = 1
> SET @.a = c2 = 2*1 -- so @.a becomes 2
> SET @.a = c2 = 2*2 -- so @.a becomes 4
> SET @.a = c2 = 2*4 -- so @.a becomes 8
> etc.
> It is documented in BOL:
> "SET @.variable = column = expression sets the variable to the same value
> as the column. This differs from SET @.variable = column, column =
> expression, which sets the variable to the pre-update value of the
> column."
> So one would assume it is safe for use.
> "Vikram Vamshi" <vikram.vamshi@.online.eclipsys.com> wrote in message
> news:%23EMjlChmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>|||Don't worry, it was pretty well hidden. Not an "undocumented" feature, but
a pretty well "barely documented" one. They specifically state in BOL that
this is how it's supposed to perform, and that's as reliable as it's gonna
get :) I would advise one thing, however: change the order of the INSERT
statements in your table, and the order of the update - and hence the values
assigned to @.a could change. I don't believe the order of rows is
guaranteed in an UPDATE statement, even if you were to put a primary key and
clustered index on it, so beware...
--begin script
if exists (select * from information_schema.tables where table_name = 't1')
drop table t1
go
create table t1(c1 char, c2 int)
insert into t1(c1) values('n') -- No longer in alphabetical order
insert into t1(c1) values('x')
insert into t1(c1) values('a') -- Also out of order
insert into t1(c1) values('e')
go
declare @.a int
set @.a = 1
update t1 set @.a = c2 = 2*@.a
go
select * from t1
-- end script
"Vikram Vamshi" <vikram.vamshi@.online.eclipsys.com> wrote in message
news:uttRxnimGHA.3732@.TK2MSFTNGP05.phx.gbl...
> Thanks Mike!!! Not sure how I missed it in the books online :)
> What about the implicit looping, is it reliable?
> For now we will go ahead and use it, If we run into any issues, I will
> post them here ...
>|||That's a little misleading. The variable will contain the value for one of
the updated rows after the UPDATE finishes, but as demonstrated it contains
the value for each row during execution. I also agree that the order of
updates is not guaranteed, so if that's important another method of updating
is required. Maybe something more like this:
--begin script
if exists (select * from information_schema.tables where table_name = 't1')
drop table t1
go
create table t1(c1 char, c2 int)
insert into t1(c1) values('a')
insert into t1(c1) values('x')
insert into t1(c1) values('n')
insert into t1(c1) values('e')
go
update t1 set c2 = (
select power(2, count(*))
from t1 a, t1 b
where a.c1 >= b.c1
and t1.c1 = a.c1
group by a.c1, a.c2
)
go
select * from t1
-- end script
"Tom Cooper" <tom.no.spam.please.cooper@.comcast.net> wrote in message
news:486dnbD6FsVvBzzZnZ2dnUVZ_oudnZ2d@.co
mcast.com...
> This might cause you potential problems with some future release, SP, or
> even just changes in your data. It is true that you can do
> @.variable = column = expression, and that updates the variable to the new
> value in the column. But Vikram is updating multiple rows, and the
> optimizer is allowed to update these rows in any order. If you are
> depending on the first row getting 2, the second row getting 4, etc, it
> won't work if the rows are updated in some other order.
> As BOL also says, "Variable names can be used in UPDATE statements to show
> the old and new values affected. This should only be used when the UPDATE
> statement affects a single record; if the UPDATE statement affects
> multiple records, the variables only contain the values for one of the
> updated rows."
> Tom
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:eyGE7zhmGHA.3352@.TK2MSFTNGP02.phx.gbl...
>