UPDATE with Oracle MERGE statement

Question

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.

Answer 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 )

Answer 2

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