X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id EDD1FD1F161
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Mon, 10 May 2004 11:17:44 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 24905-06
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Mon, 10 May 2004 11:17:23 -0300 (ADT)
Received: from news.hub.org (news.hub.org [200.46.204.72])
	by svr1.postgresql.org (Postfix) with ESMTP id 7DDF5D1F190
	for <pgsql-hackers@postgresql.org>;
	Mon, 10 May 2004 11:17:19 -0300 (ADT)
Received: from news.hub.org (news.hub.org [200.46.204.72])
	by news.hub.org (8.12.9/8.12.9) with ESMTP id i4AEHJSx065371
	for <pgsql-hackers@postgresql.org>; Mon, 10 May 2004 14:17:19 GMT
	(envelope-from news@news.hub.org)
Received: (from news@localhost)
	by news.hub.org (8.12.9/8.12.9/Submit) id i4AEBFmc062422
	for pgsql-hackers@postgresql.org; Mon, 10 May 2004 14:11:15 GMT
From: Bricklen <bricklen-rem@yahoo.comz>
User-Agent: Mozilla Thunderbird 0.6 (Windows/20040502)
X-Accept-Language: en-us, en
MIME-Version: 1.0
X-Newsgroups: comp.databases.postgresql.hackers
Subject: Re: Adding MERGE to the TODO list (resend with subject)
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>
In-Reply-To: <20040509160949.GA3897@dcc.uchile.cl>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 39
Message-ID: <c4Mnc.31976$LA4.16535@edtnps84>
Date: Mon, 10 May 2004 14:11:20 GMT
To: pgsql-hackers@postgresql.org
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No,
 hits=1.1 tagged_above=0.0 required=5.0 tests=NO_DNS_FOR_FROM
X-Spam-Level: *
X-Archive-Number: 200405/490
X-Sequence-Number: 53568

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.