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?

There's no point converting it; your where clause means the ELSE will never be true — Caius Jard, Oct 11 '19 at 12:40
I want to switch to an UPDATE beause my MERGE is a part of a 500 line plus procedure and for some reason the MERGE statement does not execute when the procedure is executed. When I run the MERGE explicitly, it gives the desired results. So wanted to see if converting this to an UPDATE will actually execute along with the procedure. — gooner_psy, Oct 11 '19 at 15:33

MT0 · Accepted Answer · 2019-10-11T13:34:15.107

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

Barbaros Özhan · Answer 2 · 2019-10-11T13:29:44.780

1

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

edited Oct 11 '19 at 13:29

answered Oct 11 '19 at 12:58

Barbaros Özhan

59,113
10
31
55

This now gives the same resulting rows as the `MERGE`. The only minor difference is that it will update every row including those rows that do not match the `t1.id = t2.id` clause that the `MERGE` filters on. This may not be an issue for a small data-set but it could result in unnecessary I/O that could create performance issues when the size of the tables scales up. – MT0 Oct 11 '19 at 13:25
I think you mean replacing the join condition `t1.ID = t2.ID` with `t1.ID = nvl(t2.ID,t1.ID)` .., don't you? – Barbaros Özhan Oct 11 '19 at 13:30
1

No, I'm talking about the `WHEN MATCHES` condition in the OP's `MERGE` statement; yes you're correlating the sub-query on `NVL(t2.id,t1.id)` but you don't have a filter to prevent rows being updated unnecessarily. If you look at the db<>fiddle then the `MERGE` statement updates only 4 rows but this query updates all 5 rows. For a large table this could result in a lot of unnecessary work. – MT0 Oct 11 '19 at 13:38

Convert MERGE statement to UPDATE statement

2 Answers2