Update alias or update tablename when from clause is specified

Question

Update alias or update tablename when from clause is specified

Viewing 4 posts - 16 through 18 (of 18 total)

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

Jeff Moden wrote:

You have to understand... I don't actually care what the question was. This isn't stack overflow. I was teaching about the proper forms and you don't need a FROM clause for a non-joined update.

I prefer it that way because it's then much easier if you need to add a join later, when you will want to assign an alias.

That is, if you write:

update tbl
set col = 'value'
from dbo.tablename tbl
where tbl.keycol = 1

and you later need to join it, you simply add the join and, if needed, another set value:

update tbl
set col = 'value', col2 = ot.data_col
from dbo.tablename tbl
inner join dbo.othertable ot ON ot.key_col = tbl.key_col
where tbl.keycol = 1

But if you wrote this (which, sure, is valid code):

update dbo.tablename
set col = 'value'
where keycol = 1

then you have to fully convert it to look like the code above, because you simply can't do a JOIN without a FROM first.

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".

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 2

There is no question in my mind that the form of updating an alias and having a FROM clause is easier in a lot of respects. I even said that previously about how easy it is to change it to a SELECT for troubleshooting purposes. That has nothing to do with what I was talking about in for the first form.

--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)

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 3

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
;