Skip to Main Content
  • Questions

  • there is a Bug using MERGE and DUAL together

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, Peyman.

Asked: November 09, 2017 - 3:02 pm UTC

Last updated: November 10, 2017 - 12:43 am UTC

Version: 11.2.0.2.0

Viewed 10K+ times! This question is

You Asked

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

and Connor said...

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.



We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.