Jim,

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

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

The need for a verb (be it REPLACE or MERGE or whatever) is not under
discussion. You questioned that a MATCHING clause was useless, because
records are always identified by a PK; I think that your assumption
doesn't hold true in the general case, as tables are not required to
have a PK, and since you can both INSERT INTO and UPDATE on such
tables, then why not allowing MERGEing on them?

J> The place where REPLACE / MERGE is really useful is when saving an
J> object cluster in relational tables where you don't care if a previous
J> definition exists, you just want to write out the current definition.

I think everybody is well aware of that. Probably 90% or even 99% of
the times an explicit MATCHING clause wouldn't be needed, but having it
available copes with 100% of the cases (i.e. the general case). A
feature that doesn't work in the general case is not a feature.

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

At this point in the discussion this is all well understood. Everybody
agrees REPLACE/MERGE is needed. The point is what to implement
exactly.

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

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

I think you are misunderstanding my position, and as such the above
sentence is out of place here. Unfortunately I don't know how to fix
that short of suggesting you re-read my initial objection and possibly
the older discussion which you seem to have missed.

J> a proper subset of the MERGE statement is what you want.

my point exactly.

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

my point exactly. We agree then. I shall now go to the beach. :-)

Ciao
--
Nando Dessena