At the Firebird Conference, Vlad described the implementation
of a REPLACE statement for Firebird 2.1. It is the short hand
equivalent for "if you find a record that matches the primary
or unique key give here, change the fields listed as listed,
otherwise store a new record with these values."

It was described as being like the MySQL REPLACE statement,
and it is exactly the same syntax. However, the MySQL
REPLACE behaves differently from the proposed Firebird
statement, and, worse, the MySQL behavior is dumb. Their
REPLACE deletes the matching record rather than updating it.

If you REPLACE a record which is the referenced record in
a foreign key relationship with children, the children will
be changed or deleted, as specified in the ON DELETE clause,
even though the result of the operation is a record with the
same value of the referenced field.

Any fields not supplied in the REPLACE statement are set to
NULL. The delete triggers fire.

The semantics of the statement suggested by Vlad for Firebird
are much better and the statement is useful. In our case,
the ON UPDATE actions of constraints would be invoked as
appropriate, and update triggers will fire. I suggest we
use a different keyword so developers who have been exposed
to MySQL don't assume we use their semantics.

Here are some possibilities....

SUPPLANT, SUBSTITUTE, SUPERSEDE, USURP, RECONSTRUCT, REBUILD,
REDO, reconstitute; reconvert; renew, renovate; regenerate;
rejuvenate, reestablish, restore, recreate...


Regards,


Ann