From: Bricklen <bricklen-rem@yahoo.comz>
To: pgsql-hackers@postgresql.org
Subject: Re: Adding MERGE to the TODO list (resend with subject)
Date: Mon, 10 May 2004 14:11:20 GMT
Message-ID: <c4Mnc.31976$LA4.16535@edtnps84> (raw)
In-Reply-To: <20040509160949.GA3897@dcc.uchile.cl>
References: <200405082246.i48MkTQ29755@candle.pha.pa.us>
<409D8E48.3070805@familyhealth.com.au>
<20040509035405.GA917@dcc.uchile.cl>
<409DAFEB.7020300@familyhealth.com.au>
<20040509051825.GB917@dcc.uchile.cl>
<409DC28A.7010509@familyhealth.com.au>
<20040509160949.GA3897@dcc.uchile.cl>
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...
>
> (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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bricklen-rem@yahoo.comz, pgsql-hackers@postgresql.org
Subject: Re: Adding MERGE to the TODO list (resend with subject)
In-Reply-To: <c4Mnc.31976$LA4.16535@edtnps84>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox