SQL UPDATE read column values before setting

Question

I have searched for this information both on SO and on google, but have not found any authoritative answer.

When you have an update statement like:

UPDATE table SET rowA = rowB, rowB = NULL ...

It seems that:

• ordering is not important (UPDATE table SET rowB = NULL, rowA = rowB)
• nonetheless, the result is that rowA takes the prev value in rowB, because it seems that UPDATE first reads the previous values, then it updates them.

I would like to know if the two above points are true in general for SQL, i.e. if they are part of the SQL UPDATE semantics, if they are in the standard, or if it is an implementation details (and therefore subject to change). Thanks!

EDIT: Let me stress that I would like an "authoritative" answer; I already tested on a number of SQL implementation that the behaviour is indeed the one depicted here. What I need is a "proof" that this is actually in the SQL standard/spec/semantics of UPDATE, with a link to the standard or, alternatively, to a surrogate reliable source (MSDN, dev.mysql.com, Oracle or PostgreSQL docs, ...)

Answer 1

James R. Groff, Paul N. Weinberg: SQL The complete reference (Osborne 1999), page 209 states

start quote

If an expression in the assignment list references one of the columns of the target table, the value used to calculate the expression is the value of that column in the current row before any updates are applied. The same is true of column references that occur in the WHERE clause. For example, consider this (somewhat contrived) UPDATE statement:

UPDATE OFFICES
 SET QUOTA = 400000.00, SALES = QUOTA
WHERE QUOTA < 400000.00

Before the update, Bill Adams had a QUOTA value of $350,000 and a SALES value of $367,911. After the update, his row has a SALES value of $350,000, not $400,000. The order of the assignments in the SET clause is thus immaterial; the assignments can be specified in any order.

end quote

The same is supported by chapter 13.9 item 6, page 393, of a draft to the ANSI-92 SQL standard (X3H2-93-004), found here.

This is the most implementaion independent and the closest I could get so far.

Other sources of X3H2-93-004 can be found e.g. here (pg 590, item 15)