Semantics of simple-seeming UPDATE statement with FROM clause

Question

Semantics of simple-seeming UPDATE statement with FROM clause

Viewing 3 posts - 16 through 17 (of 17 total)

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

Mark Dalley SSCrazy Points: 2981 More actions · Answer 1

John Mitchell-245523 - Thursday, January 26, 2017 9:36 AM
Mark Dalley - Thursday, January 26, 2017 9:23 AM
To sum up then, what people seem to be saying is that...
In order for the UPDATE to make sense, the table expression in the FROM must necessarily
involve the table whose name appears after the UPDATE keyword, and must be of such a form
that each record that is being updated relates to exactly one record in the table from which
the new value is being taken?
I know that BOL has an example where the result of the UPDATE is not actually defined, although - once again -
no error or warning is emitted.
Is that a fair description?
MarkD
Mark
Yes. The UPDATE...FROM syntax can be dangerous if you unknowingly use it where there's a one-to-many relationship. As you have observed, there's no error message - it just chooses one of the "many" values to update the "one" value to. The two examples on this page are two ways of doing the same thing; the first will warn if there's more than one value for the update, while the second won't.
John

Hmm, very interesting. If there is more than one related value it will come back with words to the effect that it can't squash multiple values into a single one.

BTW Do I surmise that the first example on the page you referred to is the ANSI standard way of doing it?

MarkD

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Mark Dalley - Thursday, January 26, 2017 10:03 AM
BTW Do I surmise that the first example on the page you referred to is the ANSI standard way of doing it?
MarkD

Yes, as far as I know, UPDATE...FROM isn't part of standard SQL.

John

Mark Dalley SSCrazy Points: 2981 More actions · Answer 3

I have been looking at The BOL page for UPDATE again, and just noticed this remark well down the page, in a sub-sub-definition:

table_or view_name
Is the name of the table or view from which the rows are to be updated. The view referenced by table_or_view_name must be updatable and reference exactly one base table in the FROM clause of the view. [emphasis mine] For more information about updatable views, see CREATE VIEW (Transact-SQL).

So I can’t say BOL didn’t try to warn me.Thanks again everyone for the helpful input.

MarkD