Re: Can I MERGE into a view?

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> 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

dana.stockler_at_hydro.com (baldo) wrote in message news:<b778fc84.0303172216.29c2aa54_at_posting.google.com>...
> The syntax diagram in the documentation seems to indicate that only a
> table can be merged into while the merge source can be a table, view,
> or query.
>
> Note 137202.1 in Metalink seems to indicate that a view CAN be merged
> into ("You cannot specify DEFAULT when updating a view.")
>
> I have not been able to create a test case allowing a merge into a
> view, but it seems to me that it should be allowed since MERGE is an
> atomic combination of INSERT and UPDATE, both of which can work on
> views.
>
> Can anyone enlighten me or point me to some definitive documentation?
>
> -
> Baldo
Received on Tue Mar 18 2003 - 20:17:28 CET