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

17 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
  11. This boils down to an understanding of where the DELETE falls in the larger syntax. It is part of the WHEN MATCHED clause, which means that it only will affect records that were matched - there is a record in the table that is matched to the values in the USING .. ON.

    Because it operates in the context of the matched records, this is expected behavior.

    ReplyDelete
  12. This boils down to an understanding of where the DELETE falls in the larger syntax. It is part of the WHEN MATCHED clause, which means that it only will affect records that were matched - there is a record in the table that is matched to the values in the USING .. ON.

    Because it operates in the context of the matched records, this is expected behavior.

    ReplyDelete
  13. How you delete the records when they are not matched in the target table? if there records that were once added in source and then were inserted into target but after time those records got deleted from source and now you want to get rid of those in target as well. Merge statement in MS sql server does this well but not sure how to do it in Oracle

    ReplyDelete
    Replies
    1. Delete them from the target table, before executing the MERGE statement. The delete section of the MERGE works as described in this blogpost.

      Delete
  14. One unfortunate behavior: Because the DELETE only happens after the row is updated, if you want to use a NULL in the source to signal that the destination row should be deleted, you would have to remove any NOT NULL constraint on the column you would update, even though the deleted row will not be committed.

    Consider the case

    CREATE TABLE FOO (ID INTEGER, Name VARCHAR2(16) NOT NULL, CONSTRAINT PK_FOO PRIMARY KEY(ID));
    CREATE SEQUENCE S_FOO_ID;
    INSERT INTO FOO (ID, Name) VALUES (1,'Fred');
    INSERT INTO FOO (ID, Name) VALUES (2,'Barney');
    INSERT INTO FOO (ID, Name) VALUES (3,'Wilma');
    COMMIT;


    Now, what I would like to say is:


    MERGE INTO FOO F
    USING (
    SELECT 1 AS ID, 'Fred Flintsone' AS Value FROM DUAL
    UNION ALL
    SELECT 3 AS ID, NULL AS Value FROM DUAL
    UNION ALL
    SELECT 4 AS ID, 'Betty' AS Value FROM DUAL
    ) X ON (F.ID = X.ID)
    WHEN MATCHED THEN UPDATE SET Name = X.Value
    DELETE WHERE Name IS NULL
    WHEN NOT MATCHED THEN INSERT(ID, Name) VALUES(X.ID, X.Value);

    However, Oracle won't let you do that because of the NOT NULL constraint on the Name, even though it will be deleting that record prior to the end of the Merge operation.

    Instead, you need to do something like:

    MERGE INTO FOO F
    USING (
    SELECT 1 AS ID, 'Fred Flintsone' AS Value FROM DUAL
    UNION ALL
    SELECT 3 AS ID, NULL AS Value FROM DUAL
    UNION ALL
    SELECT 4 AS ID, 'Betty' AS Value FROM DUAL
    ) X ON (F.ID = X.ID)
    WHEN MATCHED THEN UPDATE SET Name = NVL(X.Value, CHR(1))
    DELETE WHERE Name = CHR(1)
    WHEN NOT MATCHED THEN INSERT(ID, Name) VALUES(X.ID, X.Value);

    ReplyDelete