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: 394

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

Related posts about sql

Related posts about evaluation-order