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

From: Bricklen <bricklen-rem(at)yahoo(dot)comz>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Adding MERGE to the TODO list (resend with subject)
Date: 2004-05-10 14:11:20
Message-ID: c4Mnc.31976$LA4.16535@edtnps84
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers pgsql-sql

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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter Eisentraut 2004-05-10 15:20:02 Re: Option required to dump bytea fields?
Previous Message Rory Campbell-Lange 2004-05-10 13:40:48 Very slow query

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2004-05-10 14:21:53 Re: mingw configure failure detection
Previous Message Tom Lane 2004-05-10 13:06:59 Re: CLUSTER locking

Browse pgsql-sql by date

  From Date Subject
Next Message Hannu Krosing 2004-05-10 21:23:43 Re: Adding MERGE to the TODO list (resend with subject)
Previous Message Stef 2004-05-10 13:08:08 Exceptions when 0 rows affected.