Subject Re: [Firebird-Architect] REPLACE, again
Author Nando Dessena

A> This is what MERGE was invented for:

A> merge
A> into repositories r1
A> using delta_repositories r2
A> on (r1.repositoryName = r2.repositoryName)
A> when matched then
A> update set
A> repositoryName = r2.repositoryName,
A> schema = r2.schema,
A> sequenceName = r2.sequenceName,
A> filename = r2.filename,
A> rollovers = r2.rollovers,
A> currentVolumesystem = r2.currentVolumesystem
A> when not matched then
A> insert
A> (repositoryName, schema, sequenceName, filename,
A> rollovers, currentVolume)
A> values (r2.repositoryName, r2.schema, r2.sequenceName,
A> r2.filename, r2.rollovers, r2.currentVolume);

A> It do a "repositories right join delta_repositories on (...)", update
A> (in repositories) if the record exist in repositories and insert (in
A> repositories) if not.

A> You could change "delta_repositories" to a derived table.

A> I leave as an exercise to the reader to adapt it to run like REPLACE
A> with parameters.

I'm not sure I understand you. Is your point that the standard syntax
makes it difficult/cumbersome/impossible to implement with a
parameterized statement?

It seems to me that REPLACE is to MERGE what COALESCE (and NULLIF) is
to CASE: a special case common enough to justify a shortcut notation.
Is that correct?

Nando Dessena