14 Replies Latest reply: Mar 14, 2013 7:07 AM by FilipFry RSS

    Merge statement - delete clause

    FilipFry
      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
          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
            There is rollback between two merges

            Edited by: FilipFry on Mar 14, 2013 3:22 AM
            • 3. Re: Merge statement - delete clause
              jeneesh
              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
                I forgot to write it, but try with it
                • 5. Re: Merge statement - delete clause
                  jeneesh
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                My idea was to update all rows and after that to delete some in one command
                                • 13. Re: Merge statement - delete clause
                                  Solomon Yakobson
                                  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
                                    I did that but my first idea was with ON (1=1)