Re: Need MERGE without UPDATE clause

""NormaJean S via DBMonster.com"" <forum_at_DBMonster.com> wrote in message news:50F02DE291C40_at_DBMonster.com...
>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

just update a column to itself, ie,

merge into emp001
using emp002 on (emp001.empno = emp002.empno) when matched then update set ename = emp001.ename when not matched then insert (empno) values(emp002.empno)

however, depending on % of rows that are unnecessary updates, you might be better off doing an INSERT SELECT with an outer join in the subquery -- run some comparisons to determine what works best in your situation

++ mcs Received on Wed Jul 06 2005 - 21:43:37 CDT