Table alias with UPDATE ?

>> Is there a way to use a table alias with an UPDATE that has no FROM clause? I'm using SQL 2000. <<

You might want to get a book on Standard SQL so you will not confuse
SQL Server's highly proprietary syntax with it. The UPDATE clause
cannot have an alias because an alias is supposed to act as if new
working table has been constructed from the base table. It will
disappear at the end of the query, so updating it would be useless.
This model for aliases is consistent in Standard SQL.

>> For example: .. <<

All you need is

UPDATE TableLongName
SET myfield3 = 789
WHERE myfield1 = 123
AND myfield2 = 456;

Since there is only one table in the scope of the UPDATE clause.
 >> Stay informed about: Table alias with UPDATE ? 

Why do you think you need an alias here? You are affecting one table. The
alias and column prefixes are both superfluous. Imagine:

UPDATE TableLongName
SET myfield3 = 789
WHERE myfield1 = 123 AND myfield2 = 456






"Rav" wrote in message

> Is there a way to use a table alias with an UPDATE that has no FROM
> clause? I'm using SQL 2000. For example:
>
> UPDATE TableLongName
> SET myfield3 = 789
> WHERE TableLongName.myfield1 = 123 AND TableLongName.myfield2 = 456
>
> works, but isn't as easy to read (or write) as something like:
>
> UPDATE TableLongName T
> SET myfield3 = 789
> WHERE T.myfield1 = 123 AND T.myfield2 = 456
>
> which signals the error Incorrect syntax near 'T'.
> ** Posted from http://www.teranews.com **
 >> Stay informed about: Table alias with UPDATE ? 

UPDATE T
FROM TableLongName T
WHERE T.myfield1 = 123 AND T.myfield2 = 456

Roy Harvey
Beacon Falls, CT

On Tue, 13 May 2008 16:35:47 -0400, Rav wrote:

>Is there a way to use a table alias with an UPDATE that has no FROM
>clause? I'm using SQL 2000. For example:
>
>UPDATE TableLongName
>SET myfield3 = 789
>WHERE TableLongName.myfield1 = 123 AND TableLongName.myfield2 = 456
>
>works, but isn't as easy to read (or write) as something like:
>
>UPDATE TableLongName T
>SET myfield3 = 789
>WHERE T.myfield1 = 123 AND T.myfield2 = 456
>
>which signals the error Incorrect syntax near 'T'.
>** Posted from http://www.teranews.com **
 >> Stay informed about: Table alias with UPDATE ? 

Aaron Bertrand [SQL Server MVP] wrote:
> Why do you think you need an alias here? You are affecting one table. The
> alias and column prefixes are both superfluous. Imagine:
>
> UPDATE TableLongName
> SET myfield3 = 789
> WHERE myfield1 = 123 AND myfield2 = 456
>
>
>
> "Rav" wrote in message
>
>> Is there a way to use a table alias with an UPDATE that has no FROM
>> clause? I'm using SQL 2000. For example:
>>
>> UPDATE TableLongName
>> SET myfield3 = 789
>> WHERE TableLongName.myfield1 = 123 AND TableLongName.myfield2 = 456
>>
>> works, but isn't as easy to read (or write) as something like:
>>
>> UPDATE TableLongName T
>> SET myfield3 = 789
>> WHERE T.myfield1 = 123 AND T.myfield2 = 456
>>
>> which signals the error Incorrect syntax near 'T'.
>
>

(Sound of whacking myself upside the head.) Of course, you're right.
Thanks.
..
** Posted from http://www.teranews.com **
 >> Stay informed about: Table alias with UPDATE ? 

DOHHHHHHH!! (said in my best Homer Simpson voice)

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Rav" wrote in message

> Aaron Bertrand [SQL Server MVP] wrote:
>> Why do you think you need an alias here? You are affecting one table.
>> The alias and column prefixes are both superfluous. Imagine:
>>
>> UPDATE TableLongName
>> SET myfield3 = 789
>> WHERE myfield1 = 123 AND myfield2 = 456
>>
>>
>>
>> "Rav" wrote in message
>>
>>> Is there a way to use a table alias with an UPDATE that has no FROM
>>> clause? I'm using SQL 2000. For example:
>>>
>>> UPDATE TableLongName
>>> SET myfield3 = 789
>>> WHERE TableLongName.myfield1 = 123 AND TableLongName.myfield2 = 456
>>>
>>> works, but isn't as easy to read (or write) as something like:
>>>
>>> UPDATE TableLongName T
>>> SET myfield3 = 789
>>> WHERE T.myfield1 = 123 AND T.myfield2 = 456
>>>
>>> which signals the error Incorrect syntax near 'T'.
>>
>>
>
> (Sound of whacking myself upside the head.) Of course, you're right.
> Thanks.
> .
> ** Posted from http://www.teranews.com **
 >> Stay informed about: Table alias with UPDATE ? 

"--CELKO--" wrote in message

>>> Is there a way to use a table alias with an UPDATE that has no FROM
>>> clause? I'm using SQL 2000. <<
>
> You might want to get a book on Standard SQL so you will not confuse
> SQL Server's highly proprietary syntax with it. The UPDATE clause
> cannot have an alias because an alias is supposed to act as if new
> working table has been constructed from the base table. It will
> disappear at the end of the query, so updating it would be useless.
> This model for aliases is consistent in Standard SQL.
>

From ISO/IEC 9075-2:2003, 14.11:

UPDATE <target table> [ [ AS ] <correlation name> ]
SET <set clause list>
[ WHERE <search condition> ]

Correlation names are part of the standard SQL UPDATE. It's just that SQL
Server doesn't support them.

--
David Portas
 >> Stay informed about: Table alias with UPDATE ? 

What about this:

update t
set
t.Field1 = s.Field1,
t.Field2 = s.Field2,
t.Field3 = s.Field3
from SpecialOwner.VeryLongTableNameSource as s
inner join SpecialOwner.VeryLongTableNameTarget as t
on t.FieldChk = s.FieldChk

This is also valid syntax, but note that UPDATE...FROM is proprietary to
Microsoft SQL Server, and - most importantly - make sure the result set
returned by the JOIN operation returns at most one row from the source table
for each row in the target table.


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
 >> Stay informed about: Table alias with UPDATE ? 

On Jan 16, 4:38 am, ML wrote:
> What about this:
>
> update t
> set
>         t.Field1 = s.Field1,    
>         t.Field2 = s.Field2,
>         t.Field3 = s.Field3
> from SpecialOwner.VeryLongTableNameSource as s
>   inner join SpecialOwner.VeryLongTableNameTarget as t
>     on t.FieldChk = s.FieldChk
>
> This is also valid syntax, but note that UPDATE...FROM is proprietary to
> Microsoft SQL Server, and - most importantly - make sure the result set
> returned by the JOIN operation returns at most one row from the source table
> for each row in the target table.

Here is an example of avoiding ambiguities:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/12/08/defens...-databa
 >> Stay informed about: Table alias with UPDATE ?