SQL UPDATE order of evaluation
Posted
by pilcrow
on Stack Overflow
See other posts from Stack Overflow
or by pilcrow
Published on 2010-02-04T21:03:27Z
Indexed on
2010/04/14
13:43 UTC
Read the original article
Hit count: 416
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
© Stack Overflow or respective owner