RE: when calling UPDATE from the result set of a SELECT statement, is

#1

Most likely, you'll get the first value (BTW, remove the OREDER BY clause).
But that is not guaranteed at all. BOL explicitly documents this scenario as
undefined/nondeterministic:

"The results of an UPDATE statement are undefined if the statement includes
a FROM clause that is not specified in such a way that only one value is
available for each column occurrence that is updated, that is if the UPDATE
statement is not deterministic."

For an example of UPDATE nondeterminism at work, see
http://sqlblog.com/blogs/linchi_shea...ic-update.aspx

Linchi

"DR" wrote:

Quote:

when calling UPDATE from the result set of a SELECT statement, is the order
in which rows from the SELECT statement 100% geronteed?

tableA

myid
-----------
0
1
2
3
4
5

UPDATE tableB u
SET myid = i.myid
FROM tableA i
ORDER BY myid

Will this always update tableB with 5 since it is the last one? is this 100%
garonteed to follow the order of the source result set?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

RE: when calling UPDATE from the result set of a SELECT statement, is

microsoft.public.sqlserver.clustering microsoft.public.sqlserver.clustering

RE: when calling UPDATE from the result set of a SELECT statement, is - 12-01-2007 , 09:25 AM