Geek asks and answers

SQL UPDATE reads column values ​​before installation

I looked for this information on both SO and google, but did not find a reliable answer.

If you have an update statement, for example:

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

It seems that

  • order is not important ( UPDATE table SET rowB = NULL, rowA = rowB)
  • however, the result is that rowA takes the prev value in rowB because UPDATE seems to read the previous values ​​first and then update them.

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

EDIT: Let me emphasize that I need a "authoritative" answer; I have already tested on a number of SQL implementations that the behavior is really depicted here. I need to "prove" that this is valid in the SQL / spec / semantics UPDATE standard, with reference to the standard or, alternatively, a surrogate reliable source (MSDN, dev.mysql.com, Oracle or PostgreSQL docs, ...)

+5
source share
3 answers

James R. Groff, Paul N. Weinberg: SQL Full reference (Osborne 1999) , p. 209, contains

starting quote

, , , - . , WHERE. , ( ) UPDATE:

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

QUOTA 350 000 SALES $ 367911. SALES 350 000 , 400 000 . SET, , ; .

13.9, 6, . 393, ANSI-92 SQL (X3H2-93-004), .

, .

X3H2-93-004 , , (. 590, 15)

+4

. , , .

SQL- output

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

More articles:


All Articles