SQL UPDATE order of evaluation
- by pilcrow
What is the order of evaluation in the following query:
UPDATE tbl SET q = q + 1, p = q;
That is, will "tbl"."p" be set to q or q + 1? Is order of evaluation here governed by SQL standard?
Thanks.
UPDATE
After considering Migs' answer, I ran some tests on all DBs I could find. While I don't know what the standard says, implementations vary.
Given
CREATE TABLE tbl (p INT NOT NULL, q INT NOT NULL);
INSERT INTO tbl VALUES (1, 5); -- p := 1, q := 5
UPDATE tbl SET q = q + 1, p = q;
I found the values of "p" and "q" were:
database p q
-----------------+---+---
Firebird 2.1.3 | 6 | 6
InterBase 2009 | 5 | 6
MySQL 5.0.77 | 6 | 6
Oracle XE (10g) | 5 | 6
PostgreSQL 8.4.2 | 5 | 6
