Convert MERGE statement to UPDATE statement

Question

MERGE INTO TABLE1 t1
USING TABLE2 t2
ON (t1.ID = t2.ID) 
WHEN MATCHED THEN UPDATE 
  SET t1.PHONE_NUMBER = CASE 
                          WHEN t1.type in ('A','B') THEN  t2.phone_number 
                          ELSE NVL(t2.phone_number, t1.phone_number)
                        END

Need to convert above MERGE into an UPDATE statement.

I have tried the following:

UPDATE TABLE1 t1 
   SET t1.PHONE_NUMBER = (
                          SELECT t2.PHONE_NUMBER 
                            FROM TABLE2 t2, TABLE1 t3 
                           WHERE t3.type in ('A','B') 
                             AND t3.ID = t2.ID
                         )

How to incorporate CASE in above implementation?

Answer 1

The MERGE statement is going to be more efficient and you should probably use that; however, you could use:

UPDATE table1 t1
SET PHONE_NUMBER = NVL(
                     ( SELECT t2.phone_number
                       FROM   table2 t2
                       WHERE  t1.id = t2.id ),
                     CASE
                     WHEN t1.type IN ( 'A', 'B' )
                     THEN NULL
                     ELSE t1.phone_number
                     END
                   )
WHERE EXISTS ( SELECT 1 FROM table2 t2 WHERE t1.id = t2.id );

Oracle Setup:

CREATE TABLE table1 ( id, phone_number, type ) AS
SELECT 1, 123456, 'A' FROM DUAL UNION ALL
SELECT 2, 123456, 'B' FROM DUAL UNION ALL
SELECT 3, 123456, 'C' FROM DUAL UNION ALL
SELECT 4, 123456, 'D' FROM DUAL UNION ALL
SELECT 5, 123456, 'E' FROM DUAL;

CREATE TABLE table2 ( id, phone_number ) AS
SELECT 1, 234567 FROM DUAL UNION ALL
SELECT 2, NULL   FROM DUAL UNION ALL
SELECT 3, 345678 FROM DUAL UNION ALL
SELECT 4, NULL   FROM DUAL;

Output:

After running the update then:

SELECT * FROM table1

Outputs the same as the MERGE statement:

ID | PHONE_NUMBER | TYPE
-: | -----------: | :---
 1 |       234567 | A   
 2 |         null | B   
 3 |       345678 | C   
 4 |       123456 | D   
 5 |       123456 | E   

db<>fiddle here

---

Update:

If you look at the EXPLAIN PLAN for the MERGE statement:

| PLAN_TABLE_OUTPUT                                                               |
| :------------------------------------------------------------------------------ |
| Plan hash value: 3423411568                                                     |
|                                                                                 |
| ------------------------------------------------------------------------------- |
| | Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     | |
| ------------------------------------------------------------------------------- |
| |   0 | MERGE STATEMENT      |        |     4 |   168 |     7  (15)| 00:00:01 | |
| |   1 |  MERGE               | TABLE1 |       |       |            |          | |
| |   2 |   VIEW               |        |       |       |            |          | |
| |*  3 |    HASH JOIN         |        |     4 |   268 |     7  (15)| 00:00:01 | |
| |   4 |     TABLE ACCESS FULL| TABLE2 |     4 |   104 |     3   (0)| 00:00:01 | |
| |   5 |     TABLE ACCESS FULL| TABLE1 |     5 |   205 |     3   (0)| 00:00:01 | |
| ------------------------------------------------------------------------------- > 

Then it only reads TABLE1 and TABLE2 once each.

Compare that to the EXPLAIN PLAN for the UPDATE statement:

| PLAN_TABLE_OUTPUT                                                              |
| :----------------------------------------------------------------------------- |
| Plan hash value: 735598124                                                     |
|                                                                                |
| ------------------------------------------------------------------------------ |
| | Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     | |
| ------------------------------------------------------------------------------ |
| |   0 | UPDATE STATEMENT    |        |     4 |   168 |    23  (22)| 00:00:01 | |
| |   1 |  UPDATE             | TABLE1 |       |       |            |          | |
| |*  2 |   HASH JOIN SEMI    |        |     4 |   168 |     7  (15)| 00:00:01 | |
| |   3 |    TABLE ACCESS FULL| TABLE1 |     5 |   145 |     3   (0)| 00:00:01 | |
| |   4 |    TABLE ACCESS FULL| TABLE2 |     4 |    52 |     3   (0)| 00:00:01 | |
| |*  5 |   TABLE ACCESS FULL | TABLE2 |     1 |    26 |     3   (0)| 00:00:01 | |
| ------------------------------------------------------------------------------ |

Then it will read from TABLE1 once and TABLE2 twice; so the MERGE is likely to be a more performant query.... but you can do it with an UPDATE if you want.

db<>fiddle here

Answer 2

You can use such a nested subquery with a RIGHT JOIN :

update table1 t 
   set t.phone_number = (
                          select tt.phone_number
                            from( 
                                 select case 
                                        when t1.type in ('A','B') then  t2.phone_number 
                                        else nvl(t2.phone_number, t1.phone_number)
                                        end as phone_number, 
                                        nvl(t2.ID,t1.ID) as ID
                                   from table2 t2
                                  right join table1 t1 
                                     on t1.ID = nvl(t2.ID,t1.ID)
                                 ) tt 
                            where tt.ID = t.ID
                           );

Demo

thank you for the fiddle @MTO