Update alias or update tablename when from clause is specified

  • I would like to ask your opinions and recommendations on how to properly write an update with a from clause. I'm putting this in the SQL Server 2016 forum as that is the version we are mostly using. The syntax hasn't changed however in newer versions -as far as I know- so the same question could just as well apply to newer versions too.

    Here's the situation.  We've got code in my shop's codebase that has update statements like below in example 1: a table name is specified for the update target and that same table name is specified in the from clause, mostly with an alias but sometimes also without an alias.

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

    As opposed to example 2: where an alias is used as the target and this alias is declared in the from clause.

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

    I know both are accepted by SQL server (at least 2016 and earlier) as long as the target is an object. And also the result is correct for both alternatives(*). When the target is a cte in docs, Update (Transact-SQL) a limitation is listed in: 'Limitations and restrictions':

    When a common table expression (CTE) is the target of an UPDATE statement, all references to the CTE in the statement must match. For example, if the CTE is assigned an alias in the FROM clause, the alias must be used for all other references to the CTE. Unambiguous CTE references are required because a CTE does not have an object ID, which SQL Server uses to recognize the implicit relationship between an object and its alias. Without this relationship, the query plan may produce unexpected join behavior and unintended query results. The following examples demonstrate correct and incorrect methods of specifying a CTE when the CTE is the target object of the update operation.

    It is my opinion that the first syntax is best avoided, as I think it is not absolutely clear what the target is when this notation is used. But apart from the reference in above paragraph I can't find any explicit documentation that the "object as a target" syntax is actually allowed when a from clause is specified or that is just condoned for maybe backwards compatibilty reason or something similar. Without the from clause it is of course ANSI SQL syntax (don't pin me on the exact version) and there the target is always an object.

    What are your opinions and recommendations on whether to allow use of an object as the target or better enforce to always use an alias when using a from clause?

     

    (*) I do remember that in some early release of sql 2012 (I think it was) at one point a bug existed -which was quickly fixed- where the update with an object for its target would update the entire table instead of only the row with keycol having value 1.



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

  • "...It is my opinion that the first syntax is best avoided, as I think it is not absolutely clear what the target is..."

    How is "update dbo.tablename" unclear ?

    I think the update alias name is less clear.

    • This reply was modified 1 month, 3 weeks ago by  homebrew01.
    • This reply was modified 1 month, 3 weeks ago by  homebrew01.
  • You must use the alias to insure accurate UPDATEing.  You can, and quite often will, get inaccurate results when using the full table name instead.  (The error may now only occur when JOINing that table, I'm not sure on that. Even then, you wouldn't want the code to suddenly break just because someone added a JOIN to the UPDATE.)

    It helps if you remember that once you alias a table, the original table name is no longer valid; there is one name for a given table in a query.

    Here's a quick example to demonstrate that point:

    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!

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • homebrew01 wrote:

    "...It is my opinion that the first syntax is best avoided, as I think it is not absolutely clear what the target is..."

    How is "update dbo.tablename" unclear ?

    I think the update alias name is less clear.

    See, to each his own.  I would choose the alias.

    Why?  In the simple example given, the schema.table syntax is pretty easy to read.   But what happens if the are 10, 20, 30 tables used in the query?  As for which one is preferable,  you do realize your question is no different than asking something like what is your favorite color? You're going to get a lot of different answers.

    If there is anything, be consistent.    If you start using aliases in a query, make sure you use them always and everywhere.

    DON'T do stuff like this:

    SELECT 
    Col1,
    ABC.ColX,
    FROM Table1 T1
    INNER JOIN dbo.TableABC ON T1.Col1 = dbo.TableABC.ColX

    And, meaningful alias names make a big difference.

    When I have a developer use A, B, C and so forth, the code immediately gets rejected.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Absolutely. I use some meaningful abbreviation of the table name.

    "...And, meaningful alias names make a big difference...."

    • This reply was modified 1 month, 3 weeks ago by  homebrew01.
  • November 29, 2021 at 3:16 am

    #3957742

    REPLY | REPORT | QUOTE

    I would like to ask your opinions and recommendations on how to properly write an update with a from clause. I'm putting this in the SQL Server 2016 forum as that is the version we are mostly using. The syntax hasn't changed however in newer versions -as far as I know- so the same question could just as well apply to newer versions too.

    Here's the situation.  We've got code in my shop's codebase that has update statements like below in example 1: a table name is specified for the update target and that same table name is specified in the from clause, mostly with an alias but sometimes also without an alias.

    update dbo.tablename

    set

    col = 'value'

    from dbo.tablename tbl

    where tbl.keycol = 1

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • Thank you all for your valuable responses. As I think can easily be seen from my opening post I am a proponent of always using the update-alias syntax when a from clause is used. I was looking for new/more arguments to convince my direct colleagues to also use it.

    My reasons for choosing to always use the update alias syntax when a from is included in an update statement (i.e. when T-SQL update syntax is used) are:

    • always using one and the same pattern avoids accidental errors and improves readability.
    • update-alias-from syntax can be used in all situations, we can not use update-table-name-from for some statements.
    • having to properly link the target table name to one of the from-table-instances is additional code in sql server for an exceptional situation. It has gone wrong before, chances are it can go wrong again.
    • the update-alias-from syntax closely resembles the select-from syntax: it is very little work to transform an update-alias-from statement into a select-from statement and vice versa for example to check the selection criteria or to enclose the update statement in an if-exists statement.



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • There is no need for the FROM clause in the first example.

    I also prefer the second example because it's easy to change it to a SELECT to see what it's going to return.

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

    There is no need for the FROM clause in the first example.

    I also prefer the second example because it's easy to change it to a SELECT to see what it's going to return.

    The FROM is necessary if you want to assign an alias to the table, which seemed to me to be the whole point of the post.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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!

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ScottPletcher wrote:

    Jeff Moden wrote:

    There is no need for the FROM clause in the first example.

    I also prefer the second example because it's easy to change it to a SELECT to see what it's going to return.

    The FROM is necessary if you want to assign an alias to the table, which seemed to me to be the whole point of the post.

    That's why there's no need for the FROM clause in the first example whether that was the point of the post or not.

    Obviously there's no need for the alias, either.

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

    Jeff Moden wrote:

    There is no need for the FROM clause in the first example.

    I also prefer the second example because it's easy to change it to a SELECT to see what it's going to return.

    The FROM is necessary if you want to assign an alias to the table, which seemed to me to be the whole point of the post.

    That's why there's no need for the FROM clause in the first example whether that was the point of the post or not.

    Obviously there's no need for the alias, either.

    There is if the only reason you're asking a q about the query is how does having the alias affect the query?  And that was indeed the q.

    Without the FROM and alias, there is nothing.  With the FROM, it is critically wrong.  That's a big a difference as you can get.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

     

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

Viewing 15 posts - 1 through 15 (of 18 total)

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