Thomas Kellerer wrote:
According to various SQL documentations the following update should swap the contents of the two columns foo and bar (and it does work with Postgres, Oracle, HSQLDB, SQL Server and SQL Anywhere).
Unfortunately it does not work with Firebird (neither 1.5, nor 2.0 alpha)
INSERT INTO foobar(foo, bar) VALUES (1,2);
UPDATE foobar SET foo = bar, bar = foo;
COMMIT;
Ann W. Harrison answers:
A known and long standing bug - one that precedes the SQL standard. Firebird treats the list of assignments in an update as if they were a sequence of assignments in a code block. The SQL standard treats them as if the target were one record and the source another. We need to fix that, but haven't.
Our model is useful and defensible and a better standard (in my opinion) would have established old and new contexts for updates so both values could be referenced. However, we didn't have the $20K plus a person needed to be on the committee, so we didn't get a vote.