Table alias scope - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Table alias scope

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2010-05-17 : 11:58:16

Can I use table alias anywhere in the same store procedure?

For example, the code below is working?

select o.id, product from order o

...another code...

update o
set o.product = 'book'
where o.id = 123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-17 : 12:00:20

nope you cant. it has scope only in query you used it. so for above to work it should be


select o.id, product from order o

update o
set o.product = 'book'
from order o
where o.id = 123

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-17 : 12:01:17

No you cant

You can do


UPDATE o SET
    o.product = 'book'
FROM
    [order] AS o

Aliases are only valid for the immediate scope of the statement (or derived table etc that you alias them in).

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2010-05-17 : 12:05:42

Thank you.
As I understand, the following code is working, is it?

select o.id, product from order o

update oo
set oo.product = 'book'
from order oo
where oo.id = 123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-17 : 12:09:55

it will work. did you test it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-18 : 05:30:03

quote:
Originally posted by Sun Foster

Can I use table alias anywhere in the same store procedure?

For example, the code below is working?

select o.id, product from order o

...another code...

update o
set o.product = 'book'
where o.id = 123

look at SYNONYMS in BOL

______________________

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-18 : 10:44:52

quote:
Originally posted by ms65g

quote:
Originally posted by Sun Foster

Can I use table alias anywhere in the same store procedure?

For example, the code below is working?

select o.id, product from order o

...another code...

update o
set o.product = 'book'
where o.id = 123

look at SYNONYMS in BOL

______________________

Actually, don't you mean ALIASES?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources