Hello Ann,

Here's my reply, given the things we discussed on the conf.

I've been looking into this (in the MySQL documentation).

> 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."

Basically, a shorthand to the SQL standard MERGE command
with a single row.

> 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.

MySQL also has a command that act like a MERGE command,
but the syntax is non standard.

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

NULL or their default.

> 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.

I like the Firebird behaviour better, but I would say I agree with you
on the matter.

> Here are some possibilities....
>
> SUPPLANT, SUBSTITUTE, SUPERSEDE, USURP, RECONSTRUCT, REBUILD,
> REDO, reconstitute; reconvert; renew, renovate; regenerate;
> rejuvenate, reestablish, restore, recreate...

For me, SUPPLANT or SUBSTITUTE are prefered.




Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com