Re: Can I MERGE into a view?

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 - 08:31:44 CET