Update alias or update tablename when from clause is specified

  • 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) A socialist is someone who give you the shirt off *someone else's* back.

  • 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)
    Intro to Tally Tables and Functions

  • 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)
    Intro to Tally Tables and Functions

  • 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) A socialist is someone who give you the shirt off *someone else's* back.

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

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