Adriano dos Santos Fernandes wrote:

> 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:
>
> merge
> 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
> insert
> (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.
>
>
>

OK, I'll concede that MERGE is grotesque and that a simpler REPLACE is
warranted. This, I believe, reduces the question to whether or not the
proposed MATCHING clause makes sense.