Oracle Merge Statement – How to Use UPDATE

mergeoracle

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.

Best Answer

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 )

Related Question