UPDATE using table alias

  • Hi Gang-

    I have some stored procedures that do updates using table aliases, like this:

    UPDATE TableAlias

    SET ColVal = 1

    FROM RealTable AS TableAlias

    WHERE TableAlias.ColVal <> 1

    This allows me to include joins and stuff in the update (not shown) and make it all more readable for me.

    It all works fine, and the SSMS parser says it's fine. But I also have another script which looks at sys.sql_expression_dependencies and sys.objects to find stored procedures with invalid object references (see below), and it's understandably saying that all of the above type stored procedures have invalid references.

    SELECT

    OBJECT_NAME(DEP.referencing_id) AS referencing_name,

    DEP.referenced_entity_name

    FROM sys.sql_expression_dependencies AS DEP

    WHERE

    -- Only validate local references:

    (

    DEP.referenced_database_name = DB_NAME()

    OR

    DEP.referenced_database_name IS NULL

    )

    -- Look for references to objects that

    -- don't exist in our database:

    AND NOT EXISTS

    (

    SELECT NULL FROM sys.objects AS OBJ

    WHERE

    -- Can limit objects by type if desired:

    -- P: SQL_STORED_PROCEDURE

    -- U: USER_TABLE

    -- IF: SQL_INLINE_TABLE_VALUED_FUNCTION

    -- OBJ.[type] IN ('P', 'U', 'IF') AND

    -- Match on name rather than ID, because

    -- referenced_id is often NULL for some reason:

    OBJ.name = DEP.referenced_entity_name

    )

    GROUP BY

    OBJECT_NAME(DEP.referencing_id),

    DEP.referenced_entity_name

    ORDER BY OBJECT_NAME(DEP.referencing_id)

    So I have a couple questions.

    1. Is the UPDATE syntax I'm using kosher?

    2. Can you recommend any updates to my stored procedure validation script that will better accommodate table aliases like mine?

    Thanks!


    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

  • autoexcrement (9/21/2015)


    I have some stored procedures that do updates using table aliases, like this:

    UPDATE TableAlias

    SET ColVal = 1

    FROM RealTable AS TableAlias

    WHERE TableAlias.ColVal <> 1

    This allows me to include joins and stuff in the update (not shown) and make it all more readable for me.

    That's how we do it. We predominantly use the Table Alias "U" for the table being updated, which we think helps avoid confusion / bugs (by almost always being the same Alias Name)

    UPDATE U

    SET ColVal = 1

    FROM RealTable AS U

    WHERE U.ColVal <> 1

    and

    UPDATE U

    SET ColVal = 1

    FROM RealTable AS X

    JOIN OtherTable AS U

    ON U.SomeID = X.SomeID

    WHERE X.ColVal <> 1

  • I like the consistent "U" idea. Glad I'm not the only one using aliases in my update statements.

    Anyone else care to chime in here? Particularly with regards to SQL Server's strange misinterpretation of the table dependencies when using this syntax?


    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

  • I dislike the consistent "U" as a table alias for the table being updated for some of the same reasons that I dislike sequential table aliases, the most important one being that I like my aliases to remind me of the underlying table and U only reminds me that the table is being updated, it doesn't remind me which table it is.

    I also use SQL Prompt which can be configured to automatically add table aliases based on the table name. That also leads to consistency in table aliases while still retaining an association with the underlying table.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen (9/22/2015)


    I also use SQL Prompt which can be configured to automatically add table aliases based on the table name. That also leads to consistency in table aliases while still retaining an association with the underlying table.

    Drew

    +1 on SQL Prompt... The last time I tried using SSMS w/o it, it felt like my skin was crawling... It spoils you pretty quickly.

  • The referencing works just fine on 2016, so maybe it was a bug in that version?

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

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

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