What does the SQL standard say about "dependent" conditionals in UPDATE?

Question

Can anyone tell me what the result to the following should be according to the standard (a reference to the correct part of the standard would be welcome)

> select * from t1;
+------+
| col1 |
+------+
|    9 |
|    8 |
|   10 |
+------+
> update t1
    set col1 = col1 * 2
    where col1 <= (select avg(col1) from t1);

The point is: Does the last row get updated, since if the rows are updated in order and the average is recalculated for each row, it will satisfy the condition, or does it not get updated because any data changed by this statement will only be readable after the whole statement ran?

EDIT And what about this case?

> select * from t1;
+------+------+
| col1 | col2 |
+------+------+
|    9 |    1 |
|    8 |    2 |
|   10 |    2 |
+------+------+
> update t1 p1
    set col1 = col1 * 2
    where col1 <= (select avg(col1)
                     from t1
                     where col2=p1.col2);

Answer 1

As far as I can tell, the standard (Chapter 14.11, SQL 2003 - Foundation) is pretty clear about this:

The is effectively evaluated for each row of T before any row of T is updated

(emphasis mine)

My understanding of that sentence is that any condition (whether co-related or not) is evaluated before any row is updated.

Answer 2

About the first query, the subquery executed first so, there is no changes in average...

About second query, you are using alias in UPDATE statement but you are using alias in wrong approach.

Correct and Standard way to use alias in UPDATE statement is:

UPDATE p1
     set col1 = col1 * 2
from t1 p1
     where col1 <= (select avg(col1)
                     from t1
                     where col2=p1.col2);

Answer 3

The last row would not get updated. because "select avg(col1) from t1" is a sub-query, and it will run firstly, and store the result in the temporary table, then the update statement will execute.