What does the SQL standard say about dependent conventions in UPDATE?

Question

Can someone tell me that the result should comply with the following standard (a link 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); 

Point: is the last row updated, since if the rows are updated in order and the average value is recalculated for each row, it satisfies the condition or is not updated, since any data changed by this statement will be read only after the whole statement is completed?

EDIT 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:

Efficiently evaluated for each row T until any row T is updated

(my emphasis)

My understanding of this proposal is that any condition (whether it is connected or not) is evaluated before any row is updated.

Answer 2

On the first query, subquery is first executed, so there are no changes on average ...

In the second query, you use an alias in UPDATE , but you use an alias in the wrong approach.

The correct and standard way to use an alias in an UPDATE :

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

Answer 3

The last line will not be updated. because "select avg (col1) from t1" is a subquery, and it will work first and save the result in a temporary table, after which the update instruction will be executed.