UPDATE using table alias

Question

UPDATE using table alias

autoexcrement

SSCertifiable

Points: 5951
More actions
September 21, 2015 at 12:27 pm

#307817

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

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 1

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

autoexcrement SSCertifiable Points: 5951 More actions · Answer 2

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

drew.allen SSC Guru Points: 76944 More actions · Answer 3

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

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 4

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.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 5

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

MM

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