SQL UPDATE read column values before setting

问题:

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, ...)

回答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)

回答2:

This is standard behaviour. When you reference a row, you refer to the pre-update version.

In SQL server the two row versions can be visualized with the output clause

update  YourTable
set     col1 = col1 + 1
output  deleted.col1   -- Pre-update version of row
,       inserted.col1  -- Post-update version of row

回答3:

From the postgeSQL documentation for update (http://www.postgresql.org/docs/8.3/static/sql-update.html)

expression
An expression to assign to the column. The expression can use the old values of this and other columns in the table.