Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!border1.nntp.dca.giganews.com!border2.nntp.dca.giganews.com!nntp.giganews.com!newsfeed.iinet.net.au!newsfeed.iinet.net.au!per-qv1-newsstorage1.iinet.net.au!per-qv1-newsstorage1.iinet.net.au!per-qv1-newsreader-01.iinet.net.au!not-for-mail
Message-ID: <42CD3ACC.6060@yahoo.com>
Date: Thu, 07 Jul 2005 22:23:08 +0800
From: Connor McDonald <connor_mcdonald@yahoo.com>
Reply-To: connor_mcdonald@yahoo.com
X-Mailer: Mozilla 3.01 (WinNT; I)
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.server
Subject: Re: Need MERGE without UPDATE clause
References: <50F02DE291C40@DBMonster.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 47
NNTP-Posting-Host: 203.59.159.120
X-Trace: 1120746188 per-qv1-newsreader-01.iinet.net.au 21409 203.59.159.120
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:246776

"NormaJean S via DBMonster.com" wrote:
> 
> I have a question regarding the MERGE statment.
> Can I do a merge with no update clause?
> For example, in the merge below, when a match is found, can I  "do nothing"?
> 
> If I don't have a match, I want the insert, but if i have a match, i don't
> want to update the existing record with incoming data because the incoming
> data is not complete.
> 
> please help.   it's been a long time, i'm rusty at this.
> thanks.
> 
> =============================================
> 
>  MERGE INTO SALES_FACT D
>      USING SALES_JUL01 S
>      ON (D.TIME_ID = S.TIME_ID
>               AND D.STORE_ID = S.STORE_ID
>              AND D.REGION_ID = S.REGION_ID)
>      WHEN MATCHED THEN
>          UPDATE
>                  SET d_parts = d_parts + s_parts
>       WHEN NOT MATCHED THEN
>      INSERT (D.TIME_ID ,D.STORE_ID ,D.REGION_ID,  D.PARTS ,D.SALES_AMT ,D.
> TAX_AMT ,D.DISCOUNT)
>      VALUES ( S.TIME_ID ,S.STORE_ID ,S.REGION_ID,S.PARTS ,S.SALES_AMT ,S.
> TAX_AMT ,S.DISCOUNT);
> 
> --
> Message posted via DBMonster.com
> http://www.dbmonster.com/Uwe/Forums.aspx/oracle/200507/1

10g, the clauses become optional
-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------