Update alias or update tablename when from clause is specified

Question

Update alias or update tablename when from clause is specified

R.P.Rozema

SSChampion

Points: 12317
More actions
November 29, 2021 at 9:16 am

#3957742
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?
December 16, 2021 at 4:27 am

This was removed by the editor as SPAM
December 16, 2021 at 4:27 am

This was removed by the editor as SPAM

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

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

"...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 2 years, 12 months ago by homebrew01.
This reply was modified 2 years, 12 months ago by homebrew01.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

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

Michael L John One Orange Chip Points: 27055 More actions · Answer 3

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/

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

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

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

This reply was modified 2 years, 12 months ago by homebrew01.

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 5

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.

R.P.Rozema SSChampion Points: 12317 More actions · Answer 6

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?

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

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)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 8

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 9

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

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)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 11

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

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)