Hi Mike,

did you try

UPDATE tbl set p = q, q = q + 1;
SELECT * from tbl

P Q
=====================
5 6

I am satisfied with this result.

Regards,

Djordje Radovanovic

----- Original Message -----
From: mjp@...
To: firebird-support@yahoogroups.com
Sent: Wednesday, April 14, 2010 23:12
Subject: [firebird-support] atomicity of UPDATEs



Under 2.1.3, the following statement...

UPDATE tbl SET q = q + 1, p = q;

appears non-atomic to me, seeing two different values for "Q" while
updating
a single row. I'd have expected that "P = (Q - 1)", but Firebird does not
concur:

ISQL Version: LI-V2.1.3.18185 Firebird 2.1
Server version:
LI-V2.1.3.18185 Firebird 2.1
LI-V2.1.3.18185 Firebird 2.1/tcp (worclip)/P11
LI-V2.1.3.18185 Firebird 2.1/tcp (worclip)/P11

SQL> CREATE TABLE tbl (p INT NOT NULL, q INT NOT NULL);
SQL> INSERT INTO tbl VALUES (1, 5);
SQL> SELECT * FROM tbl;

P Q
============ ============
1 5

SQL> UPDATE tbl SET q = q + 1, p = q;
SQL> SELECT * FROM tbl;

P Q
============ ============
6 6

In my testing, one SQL engine shares FB's behavior here after the same
series of SQL commands:

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
SQLite 3.3.6 5 6

Am I misunderstanding things? What behavior, if any, is mandated by the
SQL
standards? Is future FB behavior different?

-Mike




[Non-text portions of this message have been removed]