Subject Re: [Firebird-Architect] REPLACE, again
Author Adriano dos Santos Fernandes
Jim Starkey wrote:
> 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.
This is what MERGE was invented for:

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

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

You could change "delta_repositories" to a derived table.

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