This discussion is archived
14 Replies Latest reply: Mar 14, 2013 5:07 AM by FilipFry RSS

Merge statement - delete clause

FilipFry Newbie
Currently Being Moderated
I was reading about merge statement and tried some variations, now I am pretty confused:

create table MERGE_TEST(
C1 number,
C2 varchar2(10 char),
c3 number);

insert into MERGE_TEST values(1, 'Name 3', 300);
insert into MERGE_TEST values(1, 'Name 2', 200);
insert into MERGE_TEST values(1, 'Name 1', 100);

commit;

merge into MERGE_TEST DEST
using (select 1 C1 from DUAL) SRC
on (dest.c1 = 1)
when matched then update set C3 = C3 * 10
delete where DEST.C3 > 1000
when not matched then insert values(src.c1, 1000000, 1000000);

result:
C1 C2 C3
---------- ---------- ----------
1 Name 3 3000
1 Name 2 2000
1 Name 1 1000

then:
rollback;

merge into MERGE_TEST DEST
using (select 1 c1 from dual) SRC
on (dest.c1 = src.c1)
when matched then update set C3 = C3 * 10
delete where DEST.C3 > 1000
when not matched then insert values(src.c1, 1000000, 1000000);

result:

C1 C2 C3
---------- ---------- ----------
1 Name 1 1000

Can anybody explain me why is result different in this querys?

Edited by: FilipFry on Mar 14, 2013 3:32 AM
  • 1. Re: Merge statement - delete clause
    Purvesh K Guru
    Currently Being Moderated
    It is because, The Data, present in table while the First Merge was executed had C3 column data < 1000. If you are of belief that the Update caused the data to be multiplied by 1000, then Deleted because it satisfied the Delete condition, then you are wrong there.

    Merge takes into account, the rows as at the begining of Merge statement and the rows Inserted/Updated/Deleted in Merge do not take part in any of Merge DML's.

    When, Second Merge statement is executed, the data present in Target table, has C3 column data > 1000 and hence are Deleted by the Delete statement.
  • 2. Re: Merge statement - delete clause
    FilipFry Newbie
    Currently Being Moderated
    There is rollback between two merges

    Edited by: FilipFry on Mar 14, 2013 3:22 AM
  • 3. Re: Merge statement - delete clause
    jeneesh Guru
    Currently Being Moderated
    FilipFry wrote:
    There is rollback between two merges

    Edited by: FilipFry on Mar 14, 2013 3:22 AM
    But there is NO commit after your INSERT..
  • 4. Re: Merge statement - delete clause
    FilipFry Newbie
    Currently Being Moderated
    I forgot to write it, but try with it
  • 5. Re: Merge statement - delete clause
    jeneesh Guru
    Currently Being Moderated
    Your data (After ROLLBACK) and second MERGE is shown below
    SQL> select * from merge_test;
    
            C1 C2                 C3
    ---------- ---------- ----------
             1 Name 3            300
             1 Name 2            200
             1 Name 1            100
    
    
    merge into MERGE_TEST DEST
    using (select 1 c1 from dual) SRC
    on (dest.c1 = src.c1)
    when matched then update set C3 = C3 * 10
    delete where DEST.C3 > 1000
    when not matched then insert values(src.c1, 1000000, 1000000);
    So, first it will update All C3 as C3*10 (Because all rows are MATCHING)
    Then, will delete Name2 and Name3 coz, the value of C3 > 1000, after update..

    No insert, coz, No mismatch data..

    What is your confuxion?

    What do you expect?
  • 6. Re: Merge statement - delete clause
    FilipFry Newbie
    Currently Being Moderated
    What is different in the first scenario, why it did not delete row with c3>1000?

    Edited by: FilipFry on Mar 14, 2013 3:40 AM

    Edited by: FilipFry on Mar 14, 2013 3:40 AM
  • 7. Re: Merge statement - delete clause
    Purvesh K Guru
    Currently Being Moderated
    Jeneesh, I think I understand the problem shared by OP.

    Functionally, both the Merge statements are equivalent, except the ON Condition.

    First Merge uses a Static value (1) to perform the Join whereas Second Merge uses Column Alias to do the same.

    However, First Merge, expected to Update three rows and delete two of them (those having C3 > 1000) does not delete them but just updates them. Second Merge, deletes the data from Target table which does not happen in First scenario.

    I agree, that I am also puzzled over this behaviour of Merge statement. Do you or anybody have any idea why this is happening?
  • 8. Re: Merge statement - delete clause
    FilipFry Newbie
    Currently Being Moderated
    This is also strange

    merge into MERGE_TEST DEST
    using (select 1 C1 from DUAL) SRC
    on *(1 = 1)*
    when matched then update set C3 = C3 * 10
    delete where DEST.C3 > 1000
    when not matched then insert values(src.c1, 1000000, 1000000);

    It also does not delete rows
  • 9. Re: Merge statement - delete clause
    jeneesh Guru
    Currently Being Moderated
    The output of the first statement looks like a bug..

    Anyhow, there is no chance of having an ON condition like that in a MERGE statement..
  • 10. Re: Merge statement - delete clause
    Solomon Yakobson Guru
    Currently Being Moderated
    You are definitely hitting a bug in your first MERGE. DELETE WHERE clause in WHEN MATCHED operates against updated values. In other word, row is updated first and then condition in DELETE WHERE is evaluated. And since C3 * 10 is > 1000 for all rows but "Name 1" first MERGE should delete all rows but "Name 1" like the second MERGE does. Search MetaLink for a matching bug or contact Oracle Support.

    SY.
  • 11. Re: Merge statement - delete clause
    BluShadow Guru Moderator
    Currently Being Moderated
    Yep, looks like a bug... but there again Oracle probably didn't account for people trying to merge a table but not actually specifying a valid join condition to join the source table to the merging table. Would seem like a very odd thing to do.
  • 12. Re: Merge statement - delete clause
    FilipFry Newbie
    Currently Being Moderated
    My idea was to update all rows and after that to delete some in one command
  • 13. Re: Merge statement - delete clause
    Solomon Yakobson Guru
    Currently Being Moderated
    FilipFry wrote:
    My idea was to update all rows and after that to delete some in one command
    Well you can certainly do it with MERGE by using self-reference:
    SQL> select * from merge_test
      2  /
    
            C1 C2                 C3
    ---------- ---------- ----------
             1 Name 3            300
             1 Name 2            200
             1 Name 1            100
    
    SQL> merge into MERGE_TEST DEST
      2  using (select rowid rid from merge_test) src
      3  on (dest.rowid = src.rid)
      4  when matched then update set C3 = C3 * 10
      5  delete where DEST.C3 > 1000
      6  /
    
    3 rows merged.
    
    SQL> select * from merge_test
      2  /
    
            C1 C2                 C3
    ---------- ---------- ----------
             1 Name 1           1000
    
    SQL> 
    SY.
  • 14. Re: Merge statement - delete clause
    FilipFry Newbie
    Currently Being Moderated
    I did that but my first idea was with ON (1=1)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points