Update alias or update tablename when from clause is specified

Question

Update alias or update tablename when from clause is specified

Viewing 2 posts - 16 through 17 (of 17 total)

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

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 1

ScottPletcher wrote:

Once you assign an alias to a table, that alias is the only valid way to reference that table. That's not a matter of preference or opinion. It's a SQL fact.
SELECT TOP (5) * FROM sys.objects --OK
SELECT TOP (5) sys.objects.* FROM sys.objects --OK
SELECT TOP (5) o.* FROM sys.objects o --OK
SELECT TOP (5) sys.objects.* FROM sys.objects o --ERROR! You've assigned an alias, so the original name IS NO LONGER VALID!

BWAAA-HAAA-HAAA!!! I'll meet your fact and raise you an exception... 😉

 CREATE TABLE dbo.UpdateTest
        (
         RowNum INT IDENTITY(1,1)
        ,ColA CHAR(10)
        ,ColB CHAR(10)
        )
;
 INSERT INTO dbo.UpdateTest
        (ColA,ColB)
 VALUES  ('A1','B1')
        ,('A2','B2')
;
 UPDATE dbo.UpdateTest
    SET dbo.UpdateTest.ColA = 'XX'
   FROM dbo.UpdateTest ut
;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101200 More actions · Answer 2

Jeff Moden wrote:

ScottPletcher wrote:
Once you assign an alias to a table, that alias is the only valid way to reference that table. That's not a matter of preference or opinion. It's a SQL fact.
SELECT TOP (5) * FROM sys.objects --OK SELECT TOP (5) sys.objects.* FROM sys.objects --OK SELECT TOP (5) o.* FROM sys.objects o --OK SELECT TOP (5) sys.objects.* FROM sys.objects o --ERROR! You've assigned an alias, so the original name IS NO LONGER VALID!
BWAAA-HAAA-HAAA!!! I'll meet your fact and raise you an exception... 😉
 CREATE TABLE dbo.UpdateTest
        (
         RowNum INT IDENTITY(1,1)
        ,ColA CHAR(10)
        ,ColB CHAR(10)
        )
;
 INSERT INTO dbo.UpdateTest
        (ColA,ColB)
 VALUES  ('A1','B1')
        ,('A2','B2')
;
 UPDATE dbo.UpdateTest
    SET dbo.UpdateTest.ColA = 'XX'
   FROM dbo.UpdateTest ut
;

We've already seen that SQL allows that syntax, but it's still a horrible mistake. I'm not sure why you'd want to propagate a worst practice.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".