29 July 2009

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 <= 5
7 /

Table created.

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

12 comments:

  1. Your save my time. I faced same issue with merge update/delete today. I thinked this's a bug, but found your article and take a new knowledge.
    Thanks!

    ReplyDelete
  2. thanks dear
    but if a record is deleted in SOURCE(base) table then how i can delete that record from DESTINATION(target) table using 'MERGE'
    nasir

    ReplyDelete
  3. Nasir,

    Thank you for your comment. I'm not really sure what you are looking for. When you remove a record from a table, it usually doesn't have an effect on other tables - regardless if you use MERGE or DELETE or TRUNCATE. If you want an automatic action which removes records from the Destination table when you remove records from the Source table a trigger would most likely serve your needs.

    ReplyDelete
  4. Have you got an Idea why this isn't working:

    MERGE INTO dim_ord t
    USING (SELECT *
    FROM v_dim_ord_etl
    WHERE m$id > p_max_m$id
    AND m$latest = 1) s
    ON (t.m$ord_id = s.m$ord_id)
    WHEN MATCHED THEN
    UPDATE
    SET t.m$id = s.m$id,
    t.mill = s.mill
    DELETE WHERE (s.m$src_dml = 'D')
    WHEN NOT MATCHED THEN
    INSERT(m$ord_id,
    m$id,
    ord,
    mill)
    VALUES (s.m$ord_id,
    s.m$id,
    s.ord,
    s.mill)

    A bug or?
    Where the Delete where clause just contains columns in the source table.
    But shouldn't tha automatically map back using the mapping columns. As it is now nothing is deleteted. But i f I ad the column m$src_dml to target table evrything works.

    ReplyDelete
  5. Thank you, Jonas, for your comments. I'm not really sure if I can follow your example (as I don't have your tables or data :) ). And to be honest "doesn't work" really doesn't say a lot.
    With the DELETE as part of the MERGE statement only those rows are removed which were Updated in the same MERGE statement. Perhaps send me a small example of your tables and data, do I can reproduce?

    ReplyDelete
  6. Alex,

    The problem that Jonas is having is that his DELETE WHERE predicate is referring to a column in the source table rather than a column in the target table, which is why his query doesn't delete anything.

    ReplyDelete
  7. Thanks a lot, this was really caused me a headache before i found your post;)

    ReplyDelete
  8. 1. your syntax "merge_update_clause" missed this ",".

    ReplyDelete
  9. Thanks a lot. This was insightful

    ReplyDelete
  10. Dear Alex Nuijten, your topic is very useful, but you did not consider example with 3 in 1: Update,Delete and Insert in Merge.
    I have 2 examples where Merge shows us not the same logic.
    Let me present these examples:

    1) This logic is correct:
    create table students (id number, last_name varchar2(20), score number);
    insert into students values (1, 'Ivanov', 300);
    insert into students values (2, 'Petrov', 400);
    insert into students values (3, 'Sidorov', 500);
    insert into students values (5, 'Vasilyev', 280);
    commit;
    create table students_new_info (id number, last_name varchar2(20), score number);
    insert into students_new_info values (1, 'Ivanov', 280);
    insert into students_new_info values (2, 'Petrov', 430);
    insert into students_new_info values (4, 'Mammedov', 500);
    commit;

    merge into students o
    using students_new_info n
    on (o.id=n.id)
    when matched then UPDATE SET o.score=n.score
    delete where o.score<290
    WHEN NOT MATCHED THEN INSERT values (n.id, n.last_name, n.score);

    select * from students;


    2)
    create table t_72 (id1 number, text1 varchar2(10), num1 number);
    insert into t_72 values (1,'ok',105);
    insert into t_72 values (2,'nok',106);
    insert into t_72 values (3,'nok',107);
    create table t_73 (id2 number, text2 varchar2(10), num2 number);
    insert into t_73 values (1,'nok',110);
    insert into t_73 values (2,'ok',111);
    insert into t_73 values (4,'w',112);
    commit;

    merge into t_72
    using t_73
    on (id1=id2)
    when matched then update set text1=text2
    delete where text1='nok'
    when not matched then insert values (id2,text2,num2);

    select * from t_72;

    What do we see? Row with id1 was not deleted but row with id2 was.

    Couldnt you please explain why I face with 2 different logic of merge job?


    ReplyDelete
    Replies
    1. Additional information:
      Not correct logic was found when I used TOAD.
      When I use PL/SQL everything is OK.

      Delete