Using CASE in MERGE statement : ORA-00905: missing keyword

Question

Initial Query:

MERGE INTO TABLE1 t1
USING TABLE2 t2
ON (t1.ID = t2.ID)
WHEN MATCHED 
 THEN UPDATE SET
   t1.PHONE_NUMBER = t2.PHONE_NUMBER;

I need to add another CASE in this:

CASE WHEN t1.type in ('A','B') then t2.phone_number
else nvl(t2.phone_number,t1.phone_number)

What I have tried to incorporate this :

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

Getting error : ORA-00905: missing keyword .

Can CASE statement be followed by WHEN...THEN?

Zaynul Abadin Tuhin Zaynul Abadin Tuhin · Accepted Answer · 2019-08-14T08:56:51

it would be like below

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

Using CASE in MERGE statement : ORA-00905: missing keyword

1 Answers