Question about query error received

Question

Post reply

Question about query error received

Viewing 6 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

jcdyntek Ten Centuries Points: 1153 More actions · Answer 1

You may be able to alias the table name in the update statement in Oracle but

The table name in the UPDATE can't be aliased in T-SQL or in Standard SQL.

WHug Ten Centuries Points: 1248 More actions · Answer 2

update m

set m.orderid = m2.orderid

from dbo.main m

inner join dbo.main2 m2

on m.listid = m2.listid

tyson.price SSCrazy Points: 2093 More actions · Answer 3

jcdyntek (1/13/2010)
You may be able to alias the table name in the update statement in Oracle but
The table name in the UPDATE can't be aliased in T-SQL or in Standard SQL.

Try this on SQL Server 2005

Use AdventureWorks

UPDATE a

SET [City] = 'TestIt'

FROM Person.Address a

where AddressID = 1

--Put it back to what it was

UPDATE a

SET [City] = 'Bothell'

FROM Person.Address a

where AddressID = 1

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Hello

The UPDATE and the SET apply to the same table.

Ideally the first table listed after FROM is also the target of the update.

In your case, you've aliased the target of the update in the FROM clause, but not used the alias in the UPDATE or SET

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Kit G SSCrazy Points: 2888 More actions · Answer 5

Chris Morris-439714 (1/15/2010)
Hello
The UPDATE and the SET apply to the same table.
Ideally the first table listed after FROM is also the target of the update.
In your case, you've aliased the target of the update in the FROM clause, but not used the alias in the UPDATE or SET

Ah. Thanks for the info. Thought the table name after the UPDATE needed to be the full table name and not the alias. That makes sense now. BOL isn't exactly clear on that in their description of UPDATE.

-- Kit

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Kit G (1/18/2010)
Chris Morris-439714 (1/15/2010)
Hello
The UPDATE and the SET apply to the same table.
Ideally the first table listed after FROM is also the target of the update.
In your case, you've aliased the target of the update in the FROM clause, but not used the alias in the UPDATE or SET
Ah. Thanks for the info. Thought the table name after the UPDATE needed to be the full table name and not the alias. That makes sense now. BOL isn't exactly clear on that in their description of UPDATE.

No worries Kit. Other far wiser than I figured this out and backed it up with testing long ago.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden