Skip to Main Content

Oracle Database Free

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

23c documentation bug in merge statement using the values_clause as source

Philipp SalvisbergJan 14 2024 — edited Jan 14 2024

The railroad diagram in the current version of the 23c documentation looks like this (see https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/MERGE.html#GUID-5692CCB7-24D9-4C0E-81A7-A22436DC968F__GUID-31B8B415-828F-4E43-A05F-C4C247603F69) :

According to this diagram, the following statement should work:

merge into dept t
      (values
         (-10, 'utPLSQL', 'Winterthur')
      ) as s (deptno, dname, loc)
   on (t.deptno = s.deptno) 
 when matched then
    update set t.dname = s.dname
 when not matched then
    insert (t.deptno, t.dname, t.loc)
    values (s.deptno, s.dname, s.loc);

but it throws an ORA-38107: Invalid syntax with MERGE without USING clause.

The statement works when adding the using keyword before the values_clause.

Furthermore, ON ( condition ) is required when using a table, view or a subquery as source. In other words the upper branch is also wrongly documented.

Please fix the documentation accordingly. Thank you.

This post has been answered by Chris Saxon-Oracle on Jan 15 2024
Jump to Answer
Comments
Post Details
Added on Jan 14 2024
1 comment
123 views