order matter of table column & variable in UPDATE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

order matter of table column & variable in UPDATE

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2008-06-13 : 08:07:27

Experts,

I have a sime queries as follows. I update a temp table using SQL Server UPDATE statement. What I do not understand is the order of table column and a variable in the SET clause causes one update query works but the other does not.

Any explanation would be greatly appreciated.


create table #t (ID int identity(1,1), amount money, accumulativeAmount money default(0))

create clustered index IX_t on #t(ID)

declare @accumulativeAmount money



insert #t(amount)
values(10)
insert #t(amount)
values(20)
insert #t(amount)
values(30)
insert #t(amount)
values(40)
insert #t(amount)
values(50)

--
select @accumulativeAmount= 0.00


-- problem version 
update t
set  accumulativeAmount = @accumulativeAmount = amount + @accumulativeAmount
from #t t


-- working version
update t
set @accumulativeAmount = accumulativeAmount = amount + @accumulativeAmount
from #t t


-- check
select * from #t

-- drop
drop table #t

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 08:12:21

thats how SET works the syntax for SET is
SET
{ @local_variable
[:: property_name | field_name ] = expression | udt_name { . | :: } method_name(argument [ ,...n ] )
}

so variable name should always come on left hand side.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources