Consider please the follwing simple table:
create table table_1 (c1 varchar2(100), c2 varchar2(100));
If we apply the following MERGE command now (attend please the WHERE clause), we get:
merge into table_1 tb using (select 'c1 value' as c1, 'c2 value' as c2 from dual where 1=2) v on (tb.c1=v.c1)
when matched then update set tb.c2=v.c2
when not matched then insert (c1,c2) values(v.c1, v.c2);
1 rows merged.select * from table_1;
C1 C2
--------------------------------------------------
c1 value c2 value
Even though the query in the USING clause returns no row. Could you please contact the support team in order to fix this Bug!!
Thank you for your answer
You should move to the latest patchset...
SQL> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> create table t (c1 varchar2(100), c2 varchar2(100));
Table created.
SQL>
SQL>
SQL> merge into t tb
2 using (select 'c1 value' as c1, 'c2 value' as c2 from dual where 1=2) v
3 on (tb.c1=v.c1)
4 when matched then update set tb.c2=v.c2
5 when not matched then insert (c1,c2) values(v.c1, v.c2);
0 rows merged.