Subject Re: [firebird-support] UPDATE Bug ?
Author Ann W. Harrison
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;

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.