Why doesn't this update sum up ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Why doesn't this update sum up ?

Author

Topic

PaladinZ06
Starting Member

3 Posts

Posted - 2005-10-25 : 12:33:34

select 0 as int1 into test1

select 1 as int2 into test2
insert into test2 (int2) values (7)
insert into test2 (int2) values (5)

update test1
set int1 = int1 + int2 from test2

select * From test1

Results:
int1
1

(1 row(s) affected)

I can't be sure, but I thought this approach worked in some version of MS SQL. Any ideas?

-Paladin

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-25 : 13:01:19

Any ideas for what? I missed the question.

What this is doing is updating test1 3 seperate times. 0+7, 0+5, and 0+1. So your "select * from test1" just happens to show the 0+1 update. Even though your code doesn't raiise any errors, it is quite ambiguous, it's hard to understand what you're trying to accomplish. What is your desired result? 13? If so:


update test1 set 
   int1 = (select sum(int2) from test2)
from   test1
where  int1 = 1

Be One with the Optimizer
TG

PaladinZ06
Starting Member

3 Posts

Posted - 2005-10-25 : 13:24:20

If each UPDATE actually performed its task, then the value would increment. I thought that at one point in MS SQL history, my code would have returned 13.

Obviously my example is highly abstracted from real issues.

I must not have enough coffee in me.

Using virtual tables or subqueries is indeed hwo I've done things.

Paladin

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-10-25 : 13:28:13

I think paladin is after the update with variable "trick" (which is in BOL).

TG, I understand that there is only 1 update in table Test1 : 0 + ANYoneOF(7,5,1).

create table test1(int1 int primary key, int2 int null)
insert test1(int1) select 1 union all select 7 union all select 5

select * from test1

declare @i int; set @i = 0
update test1 set
	@i = int2 = int1 + @i

select * From test1

set @i = 0
update test1 set
	@i = int1 = int1 + @i

select * From test1

drop table test1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources