Re: MERGE and WITH ?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 17 Oct 2006 08:47:22 +0100
Message-ID: <XICdnccv0YwWF6nYnZ2dnUVZ8tGdnZ2d@bt.com>

"klabu" <nosuchuser_at_gmail_dot_com> wrote in message news:12j7gqbhc4042d4_at_corp.supernews.com...
> Does MERGE work with "WITH" clause ???
> like:
>
> WITH
> sub_sql
> AS
> (some_select)
> MERGE INTO targettable
> USING sub_sql
> ........
>
> thanks
>

I am curious - is there any problem with creating a little test case to find out ?

The answer is no - but (in 10g at least) you CAN do the following:

merge
 into old_data od
using (

          with m_subq as(
          select
           *
          from new_data
          where mod(id,50) = 0
          )
          select * from m_subq

 ) nd
on (

   od.id = nd.id
  and od.small_vc = nd.small_vc
 )
when matched then
 update set od.padding = nd.padding
when not matched then
 insert values ( nd.id, nd.small_vc, nd.padding ) ;

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

Received on Tue Oct 17 2006 - 02:47:22 CDT