Re: Adding MERGE to the TODO list (resend with subject)

Alvaro Herrera wrote:

<snip>
> Oh, I see. Complex stuff ... I wonder how will it work with sequences
> -- if one insertion fails and we have to try again, there's a chance a
> sequence could be advanced more than once. Note the article skips the
> "signal-statement" symbol (is it present in SQL99? What does it do?)
>
> I also wonder if there will be a corresponding RULE implementation ...
>
> The full DB2 reference is at
> http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0010873.htm
>
> (signal-statement is something to raise an exception, apparently)
>
> (I wonder why they don't use BNF syntax anymore ...)
>
Just to add to this information, Oracle 9i and 10g have also implemented
the MERGE command. 9i offers an update/insert, whereas 10g adds a delete
option as well, which is rather handy.
'Purpose', quoted from:
http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10759/statements_9016.htm#sthref7014

(note, to view this link, you will need to sign up for a free OTN acct.)

"Use the MERGE statement to select rows from one or more sources for
update or insertion into one or more tables. You can specify conditions
to determine whether to update or insert into the target tables.

This statement is a convenient way to combine multiple operations. It
lets you avoid multiple INSERT, UPDATE, and DELETE DML statements.

MERGE is a deterministic statement. That is, you cannot update the same
row of the target table multiple times in the same MERGE statement."

Point being, I've found the delete option very useful too, rather than
having to do the same procedurally.