DELETE in the MERGE statement

The Merge statement was introduced in Oracle 9i and improved upon in Oracle 10g. In Oracle 9i only the INSERT and UPDATE parts were supported, in Oracle 10g DELETE was added. The "merge_update_clause" and "merge_insert_clause" became optional. The basic syntax for the MERGE statement:

DELETE can only occur in the "merge_update_clause" of the above schema. This means that it must occur in the WHEN MATCHED THEN clause. Until recent, I missed this part of the description of the "merge_update_clause" concerning the DELETE operation. First I will show you what I thought, then I’ll show you where the behavior is documented.

First we’ll create a table with two columns:

SQL> select *
  2    from v$version
  3  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
NLSRTL Version 10.2.0.3.0 - Production

SQL>
SQL> create table t
  2  as
  3  select rownum rn
  4       , 'A' ind
  5    from all_objects
  6   where rownum  update t
  2     set ind = 'D'
  3   where rn = 5
  4  /

1 row updated.

SQL>
SQL> select *
  2    from t
  3  /

        RN I
---------- -
         1 A
         2 A
         3 A
         4 A
         5 D

Notice that the last record, with RN 5, has an Ind "D". Next we will merge a record into this table.

SQL> merge into t
  2  using (select 3 i
  3              , 'D' ind
  4           from dual
  5        ) dat
  6     on (t.rn = dat.i)
  7   when matched then
  8     update set t.ind  = dat.ind
  9     delete where t.ind = 'D' --<--- Here is the DELETE
 10  /

1 row merged.

Only one row? Shouldn’t that be two rows? One for the UPDATE and one for the DELETE? Question for you: Which record(s) is (are) affected by this statement? My wrong assumption was this: Record with RN 3 has had the IND column changed to "D" and all records with IND "D" are removed. Effectively removing records with RN 3 and 5. Now the quote from the documentation.

The only rows affected by this clause are those rows in the destination table that are updated by the merge operation.

This means that records in the destination table are not deleted when they are not updated by the MERGE first.

SQL> select *
  2    from t
  3  /

        RN I
---------- -
         1 A
         2 A
         4 A
         5 D

As you can see the record with RN 5 is still in the table. Because it was not updated in the merge, it was not deleted. In order to remove some records from the table using the MERGE statement, you need to update these records first. It is not possible to dismiss the UPDATE statement from the MERGE:

SQL> merge into t
  2  using (select 3 i
  3              , 'D' ind
  4           from dual
  5        ) dat
  6     on (t.rn = dat.i)
  7   when matched then
  8     delete where t.ind = 'D' --<--- Here is the DELETE
  9  /
   delete where t.ind = 'D' --<--- Here is the DELETE
   *
ERROR at line 8:
ORA-00905: missing keyword

… learn something every day.

documentation link
crosspost