0

I have following MERGE statement. Table table_A contains list of ID (cli_id) and column fb_flag. The query inside update the column fb_flag in table_A when cli_id is matching. Which works.

However when I want insert something else when the IDs are not matching I have difficulties.

See my two attempts in comments.

MERGE INTO tab_A chi
USING (SELECT    cli_id,
                 'TRUE' AS free_book_b
      FROM       tab_freebook d) fb
ON (chi.cli_id = fb.cli_id)
WHEN MATCHED THEN
  UPDATE SET chi.fb_flag = fb.free_book_b;
--first attempt
/* WHEN NOT MATCHED THEN
     UPDATE SET chi.fb_flag = 'FALSE'; */
--second attempt
/* WHEN NOT MATCHED THEN
     INSERT (chi.cli_id)
     VALUES ('FALSE')
     WHERE chi.cli_id IS NULL; */

Thank you for any advice.

EDIT:

Expected result:

tabA

| cli_id |   fb_flag   |
------------------------
|   1    |     NULL    |
|   2    |     NULL    |
|   3    |     NULL    |
|   4    |     NULL    |

tab_freebook

| cli_id | free_book_b |
------------------------
|   23   |     TRUE    |
|   2    |     TRUE    |
|   3    |     TRUE    |
|   6    |     TRUE    |

What I need have in tabA

| cli_id |   fb_flag   |
------------------------
|   1    |     FALSE   |
|   2    |     TRUE    |
|   3    |     TRUE    |
|   4    |     FALSE   |

I have found temporary solution - when I am creating tabA, I fill it with 'FALSE' and then the merge update only the columns where the condition is met.

Which gives me expected result, but it's not really nice and clean as with just merge.

  • I'm not sure but it looks a little as though you want a NOT MATCHED BY SOURCE clause, which Oracle's MERGE probably doesn't support. – Andriy M Nov 27 '15 at 14:28
1

Try this ...

  create table taba ( cli_id  number, fb_flag varchar2(10) );
  insert into taba values ( 1, null );
  insert into taba values ( 2, null );
  insert into taba values ( 3, null );
  insert into taba values ( 4, null );

  create table tab_freebook ( cli_id  number, free_book_b varchar2(10) );
  insert into tab_freebook values ( 23, 'true' );
  insert into tab_freebook values ( 2, 'true' );
  insert into tab_freebook values ( 3, 'true' );
  insert into tab_freebook values ( 6, 'true' );

  commit;

  select * from taba;

      CLI_ID FB_FLAG
  ---------- ----------
           1
           2
           3
           4

  select * from tab_freebook;

      CLI_ID FREE_BOOK_
  ---------- ----------
           2 true
           3 true
           6 true
          23 true


  select a.cli_id, a.fb_flag  old_val,
         nvl(tf.free_book_b, 'FALSE') new_val
    from taba  a
        LEFT OUTER JOIN tab_freebook tf
           ON a.cli_id = tf.cli_id
  /

      CLI_ID OLD_VAL    NEW_VAL
  ---------- ---------- ----------
           2            true
           3            true
           4            FALSE
           1            FALSE

  SQL>

  MERGE INTO taba  base
     using ( 
              select a.cli_id, a.fb_flag  old_val,
               nvl(tf.free_book_b, 'FALSE') new_val
          from taba  a
              LEFT OUTER JOIN tab_freebook tf
                 ON a.cli_id = tf.cli_id
           )  new
     ON ( new.cli_id = base.cli_id )
     WHEN MATCHED THEN UPDATE
           SET base.fb_flag = new.new_val
  /

  4 rows merged.

  select * from taba;

      CLI_ID FB_FLAG
  ---------- ----------
           1 FALSE
           2 true
           3 true
           4 FALSE

  SQL>

[edit] fixed typo - somehow my initial test missed your test record "23" .. fixed, same result ;) [/edit]

Basically, start by writing a SELECT query ONLY that returns the end result set you want. Then use that in your USING clause .. and viola ... "magic" ;)

although that said, you could probably avoid the MERGE altogether and just use an UPDATE ;) (that said, I actually use MERGE now more often than a straight UPDATE when the logic starts to get a little complex ... that is, for simple UPDATES, I use UPDATE, for complex UPDATEs, I use MERGE ;) I just find the syntax easier ... shrug )

  • That's actually nice out-of-box solution. But I am afraid about the performance. I think in my case, i will stick to pre-fill the columns with 'FALSE'. Thank you! – HonzaB Nov 30 '15 at 9:01
  • @HonzaB: Don't "be afraid of performance" .. test it ... prove it. If you have indexes on cli_id ... it should perform just fine. If not, you could try pulling the rowid out in the "new" subquery .. and use the rowid's to link ... but if the inner query runs fine, the merge should be fine as well. Test it .. don't "be afraid" .. ;) – Ditto Nov 30 '15 at 13:46
0

Below is an example.

SQL> create table t1 as select rownum as id, 'FALSE' as value from dual connect by level <= 3;

Table created.

SQL> create table t2 as select rownum + 2 as id from dual connect by level <= 3;

Table created.

SQL> select * from t1;

        ID VALUE
---------- -----
         1 FALSE
         2 FALSE
         3 FALSE

SQL> select * from t2;

        ID
----------
         3
         4
         5

Now the merge statement.

SQL> merge into t1 a
        using 
        (
          select id, 'TRUE' as value from t2
        ) b
        on (a.id = b.id)
        when matched then
          update set a.value = b.value
        when not matched then
          insert (a.id, a.value) values (b.id, 'FALSE');

SQL> select * from t1 order by id;

        ID VALUE
---------- -----
         1 FALSE
         2 FALSE
         3 TRUE
         4 FALSE
         5 FALSE
  • Thanks, unfortunately that doesn't work for me - I get updated only rows where the condition is matched. I put expected result in my question. Maybe that will help. – HonzaB Nov 27 '15 at 17:07
  • @HonzaB MERGE alone is not capable of that, you need a MERGE and an UPDATE. – Balazs Papp Nov 27 '15 at 18:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.