SQL UPDATE order of evaluation

user_132382 0
Viewed 5581

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  -- But see "Update 2" below
InterBase 2009   | 5 | 6
MySQL 5.0.77     | 6 | 6  -- See "Update 3" below
Oracle XE (10g)  | 5 | 6
PostgreSQL 8.4.2 | 5 | 6
SQLite 3.3.6     | 5 | 6
SQL Server 2016  | 5 | 6

UPDATE 2

Firebird 2.5 changes its behavior to match the majority of other SQL engines I tested, leaving MySQL alone. The relevant Release Notes entry, "Logic Change in SET Clause", strongly suggests that the majority behavior is correct per SQL specifications.

I've bugged MySQL to comment on this behavior (bug no. 52861), as they seem to be the outlier.

UPDATE 3

The aforementioned bug is today (2010-05-19) closed, and the documentation set to be updated to make this behavior explicit both in the UPDATE description and in the Differences from Standard SQL section.

Bravo, MySQL.

evaluation operator-precedence sql-update sql
3 Answers

The UPDATE does not see the results of its work.

p will be set to q as of before update.

The following code will just swap the columns:

DECLARE @test TABLE (p INT, q INT)

INSERT
INTO    @test
VALUES  (2, 3)

SELECT  *
FROM    @test

p    q
---  ---
  2    3

UPDATE  @test
SET     p = q,
        q = p

SELECT  *
FROM    @test

p    q
---  ---
  3    2
user_551590

The write to the table has to occur after transaction that was well under way when the read was completed.

user_1244860
Related