Nando Dessena wrote:

> Jim, all,
>
>
>>>> REPLACE INTO <table> [(<field_list>)] VALUES (<value_list>) MATCHING
>>>> (<field_list>) [RETURNING <value_list>]
>>>>
>
> J> The MATCHING clause makes no sense, even if you do like it, since the
> J> <field_list> must contain the primary key
>
> why? Matching on a secondary key and having a before insert trigger
> generate the primary key on insert, while not something I do very
> often myself, looks reasonable to me.
>

That would require that you can uniquely indentify a record by some
means other than a primary key. Possible, sure, but is it of sufficient
general utility to implement as a separate verb? I doubt it.

The place where REPLACE / MERGE is really useful is when saving an
object cluster in relational tables where you don't care if a previous
definition exists, you just want to write out the current definition. A
typical snippet would be something like this:

void Repository::save()
{
Sync sync (&database->syncSysConnection, "Repository::update");
sync.lock (Shared);
PreparedStatement *statement = database->prepareStatement (
"replace into system.repositories
(repositoryName,schema,sequenceName,filename,rollovers,currentVolume)"
"values (?,?,?,?,?,?)");
int n = 1;
statement->setString (n++, name);
statement->setString (n++, schema);
statement->setString (n++, sequence->name);
statement->setString (n++, filePattern);
statement->setString (n++, rolloverString);
statement->setInt (n++, currentVolume);
statement->executeUpdate();
statement->close();
sync.unlock();
database->commitSystemTransaction();
}

Without replace you'd have to compile and and execute a update ... where
checking the return count, and if zero compile and execute an insert. A
pain in the butt, particularly since the pattern occurs so often.

> Plus, injecting smartness in the verb, which becomes aware of the
> primary key and dependant on it, would look inconsistent with the rest
> of the language to me.
>

Sorry, but the rest of the database world doesn't agree with you here.
You may be right, of course, but Oracle, Sybase, Microsoft, MySQL,
Falcon/Netfrastructure, and the SQL Standard looked at the same problem
and came to a different conclusion.

> Finally, if an established syntax (MERGE in the SQL standard) exists,
> although cumbersome and/or seemingly too complex for most uses,
> inventing an additional verb seems wrong to me. It would be better to
> partially support the MERGE statement than creating a smaller brother.
>
>
>

I don't think the problem is with the SQL Standard MERGE statement but
with the understanding of it. When I hear a technical argument that
something is "confusing", more often than not it was the speaker who was
confused. I don't happen to have a copy of the SQL standard with me
(we're anchored between a couple of small islands five miles off the
coast of New Hampshire), but I strongly suspect that once the ridiculous
language the SQL standard tends to use is unraveled, a proper subset of
the MERGE statement is what you want.

I have long argued the position vis a viz the SQL Standard the doctrine
of "no arbitrary differences". If a subset of the standard MERGE
statement meets the requirements for Firebird, then that subset should
be selected for implementation. There is no reason to have an
incompatible statement in the same ecological language niche.