--- In IB-Architect@y..., Helen Borrie <helebor@w...> wrote:

> At 05:31 PM 09-02-01 -0500, you wrote:
>
> >A place where this is particularly useful is a (non-standard)
> >REPLACE verb, syntactically parallel to INSERT but does
> >an UPDATE if a record with the same primary key exists,
> >a non-op if it exists and nothing has changed, otherwise
> >an INSERT. Trivial to implement, saves a bundle of
> >application level code, and fast to boot.
>
> Yow! I want one! (Along with CASE and NULLIF for a perfick

world...)

(I would trade NULLIF for derived tables, but who asked.)

Not that anyone would care, but there was a proposal from ANSI to add
a statement to standard SQL, post SQL99, with a similar function but
different name -- the keyword they chose was MERGE.

(I've also seen a similar thing implemented as UPSERT. Cute.)

I don't know if the ANSI proposal has been accepted or not, but the
preamble to the proposal described the statement as follows:

"Given a target and source table and a predicate, <merge statement>
updates the rows in the target table that match the rows in the
source
table with the values in the corresponding matching rows and inserts
rows from the source table that do not match any rows in the target
table. Users can control whether only updates, only inserts or both
updates and inserts happen."

Whatever,
db