Re: Can I MERGE into a view?

Maybe user privilege was not set up correctly? Maybe the definition of the view was changed? Views may or may not be updatable. If they are updatable, then you should be able to merge into them. In any case, good to know that it worked for you :-)

• Jusung Yang

dana.stockler_at_hydro.com (baldo) wrote in message news:<b778fc84.0303182331.6e793702_at_posting.google.com>...
> Thanks for your input confirming that merge into a view works.
>
> I went back to my test case and, wonder of wonders, it now worked.
>
> I spoke with the developer who originally raised the question and he
> ran his test case with similar suprising success.
>
> I suppose I must have made *some* database change that affected the
> test cases, but I'm darned if I know what.
>
> Anyway, thanks for taking the time to answer.
>
> -
> Baldo
>
> JusungYang_at_yahoo.com (Jusung Yang) wrote in message news:<130ba93a.0303181117.4dbd6efd_at_posting.google.com>...
> > It seems it depends on whethere or not you can modify the data in the
> > base table of the view. If you can modify the data with regular SQL,
> > you should be able to merge. Here is an example:
> >
> > SQL> create or replace view test2_vw as select * from test2;
> >
> > View created.
> >
> > SQL> desc test2
> > Name Null? Type
> > ----------------------------------------- --------
> > ---------------------------
> > C1 VARCHAR2(2)
> > C2 NUMBER(1)
> >
> > SQL> desc test3
> > Name Null? Type
> > ----------------------------------------- --------
> > ---------------------------
> > C1 VARCHAR2(2)
> > C2 NUMBER(1)
> > C3 NUMBER(2)
> >
> > SQL> create or replace view test2_vw as select * from test2;
> >
> > View created.
> >
> > SQL> merge into test2_vw t2
> > 2 using (select * from test3) t3
> > 3 on (t2.c1=t3.c1)
> > 4 when matched then
> > 5 update set t2.c2=t3.c2
> > 6 when not matched then
> > 7 insert (c1,c2) values(t3.c1, t3.c2);
> >
> > 4 rows merged.
> >
> > SQL>
> >
> >
> > - Jusung Yang
Received on Wed Mar 19 2003 - 17:42:06 CET