when calling UPDATE from the result set of a SELECT statem..

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?

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/archive/2007/11/13/nondeterminist...update.

Linchi

"DR" wrote:

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