how to make an alias here ?

Hi experts,
i am working with SQL-Server 2000 and have a special problem in one of my
stored procedures. To be as fast as possible i use a temp table defined as
variable. And i want to calculate a value in this table depending on values
in the same table. But i can't make it work. Here my problem:
-- can be copied into QueryAnalizer --
declare @.tbl table (
id1 int,
id2 int,
w1 int,
w2 int )
insert into @.tbl values (1, 2, 12, null)
insert into @.tbl values (2, 3, 10, null)
insert into @.tbl values (3, null, 7, null)
-- this is my wish. It doesn't work, because alias t1 is not valid, but i
think you can see what i want to do:
-- update @.tbl t1 set t1.w2 = t1.w1 - IsNull((select IsNull(t2.w1,0) from
@.tbl t2 where t2.id1 = t1.id2),0)
-- and this i all i can do, but with wrong result:
update @.tbl set w2 = w1 - IsNull((select IsNull(w1,0) from @.tbl t2 where
t2.id1 = id2),0)
select * from @.tbl
----
gives me:
1 2 12 12
2 3 10 10
3 NULL 7 7
and this is what it want to have:
1 2 12 2
2 3 10 3
3 NULL 7 7
I think, the problem is because in ... where t2.id1 = id2) the value of id2
is not the value of the atually updating record, it stays on id2 of the
last insert, which means, id2 is always NULL. And i can't use an alias (why
is an alias here not possible? This is not clear to me). What can i do?
thanks,
Helmuthelmut woess wrote:
> Hi experts,
> i am working with SQL-Server 2000 and have a special problem in one
> of my stored procedures. To be as fast as possible i use a temp table
> defined as variable. And i want to calculate a value in this table
> depending on values in the same table. But i can't make it work. Here
> my problem:
> -- can be copied into QueryAnalizer --
> declare @.tbl table (
> id1 int,
> id2 int,
> w1 int,
> w2 int )
> insert into @.tbl values (1, 2, 12, null)
> insert into @.tbl values (2, 3, 10, null)
> insert into @.tbl values (3, null, 7, null)
> -- this is my wish. It doesn't work, because alias t1 is not valid,
> but i think you can see what i want to do:
> -- update @.tbl t1 set t1.w2 = t1.w1 - IsNull((select IsNull(t2.w1,0)
> from @.tbl t2 where t2.id1 = t1.id2),0)
>
update t1 set t1.w2 = t1.w1 - IsNull((select IsNull(t2.w1,0)
from @.tbl t2 where t2.id1 = t1.id2),0)
from @.tbl t1
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Am Thu, 15 Dec 2005 09:16:21 -0500 schrieb Bob Barrows [MVP]:

> helmut woess wrote:
> update t1 set t1.w2 = t1.w1 - IsNull((select IsNull(t2.w1,0)
> from @.tbl t2 where t2.id1 = t1.id2),0)
> from @.tbl t1
Incredible, how fast you came up with the solution!
thousand thanks, Bob, you saved me a lot of time!
Helmutsql