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.

• Andriy M: 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.

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 )

• HonzaB: 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!
• Ditto: @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" .. ;)

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

• HonzaB: 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.
• Balazs Papp: @HonzaB MERGE alone is not capable of that, you need a MERGE and an UPDATE.