Clubbing update statements using case in oracle

Question

I am trying to update the same column with different error codes based on the error condition.

The update conditions (Separately) :

UPDATE MEM_SRC_EXTN
SET INT_VALUE_1 =100
WHERE ISNULL(TYPE_VALUE,'') =''
OR ISNULL(TYPE,'') =''
OR ISNULL(VALUE_1,'') =''
OR ISNULL(VALUE_2,'') ='';

UPDATE MEM_SRC_EXTN EXTN
SET INT_VALUE_1=222
WHERE NOT EXISTS 
(SELECT 1
FROM CMC_MEM_SRC SRC 
WHERE EXTN.TYPE_VALUE = SRC.TN_ID);

UPDATE MEM_SRC_EXTN
SET INT_VALUE_1=333 
GROUP BY TYPE_VALUE 
HAVING COUNT(VALUE_1)>1;

As I have to club these 3 update conditions into one based on condition, so that It first checks the condition and then update column values instead of triggering all 3 update statement each time.

I come up with this solution but not sure will whether this will work or not?

UPDATE MEM_SRC_EXTN EXTN
SET INT_VALUE_1 = CASE 
                          WHEN (ISNULL(TYPE_VALUE,'') ='' OR ISNULL(TYPE,'') ='' OR ISNULL(VALUE_1,'') =''OR ISNULL(VALUE_2,'') ='')
                          THEN 100
                          WHEN NOT EXISTS (SELECT 1 FROM CMC_MEM_SRC SRC WHERE EXTN.TYPE_VALUE = SRC.TN_ID)
                          THEN 222
                          WHEN GROUP BY TYPE_VALUE HAVING COUNT(VALUE_1)>1
                          THEN 333
                          ELSE 000
                        END

That is indeed the correct approach. Test it and write back if it's not working for some reason. — , Oct 31 '16 at 13:52
There is no `isnull` function in Oracle, use `coalesce` or `nvl` instead - see http://stackoverflow.com/questions/3523036/what-is-the-oracle-equivalent-of-sql-servers-isnull-function — Tony Andrews, Oct 31 '16 at 13:59
Also, an empty string is considered `NULL` in Oracle. So, `NVL(TYPE_VALUE,'') = ''` will be `FALSE` if `TYPE_VALUE` is `NULL`. I expect you want `NVL(TYPE_VALUE,'#NULL#') = '#NULL#'`, or something like that, where '#NULL#' is some generic string that could not be an actual value of `TYPE_VALUE`. — Matthew McPeak, Oct 31 '16 at 14:12
Also `WHEN GROUP BY`... that's not right at all. Someone needs to answer this question. — Matthew McPeak, Oct 31 '16 at 14:14

score 0 · Answer 1 · answered Oct 31 '16 at 14:18

The first of all you should use correct order of statement. If WHEN GROUP BY TYPE_VALUE HAVING COUNT(VALUE_1)>1 was last then in case it should be first. Besides you cant write WHEN GROUP BY TYPE_VALUE HAVING COUNT(VALUE_1)>1. = '' in orcle is the same as = null it's not true and not false use is null You may rewrite it to subquery

UPDATE MEM_SRC_EXTN EXTN
SET INT_VALUE_1 = CASE 
     WHEN exists (SELECT NULL 
                    FROM MEM_SRC_EXTN sub_extn
                   WHERE sub_extn.TYPE_VALUE = EXTN.TYPE_VALUE 
                      OR (sub_extn.TYPE_VALUE is NULL AND EXTN.TYPE_VALUE IS NULL)
                   GROUP BY TYPE_VALUE 
                  HAVING COUNT(VALUE_1)>1)
       THEN 333
     WHEN NOT EXISTS (SELECT 1 
                        FROM CMC_MEM_SRC SRC 
                       WHERE EXTN.TYPE_VALUE = SRC.TN_ID)
       THEN 222
     WHEN TYPE_VALUE is null OR TYPE is null OR VALUE_1 is null OR VALUE_2 is null
       THEN 100
     ELSE 000
     END

but this query are agly and may be you may use something else. Please think about merge

score 0 · Accepted Answer · answered Oct 31 '16 at 14:23

I would consider a MERGE statement for this. Like so:

MERGE INTO mem_src_extn t USING 
( 
SELECT mse.rowid row_id,
       CASE WHEN mse.type_value IS NULL OR mse."TYPE" IS NULL OR mse.VALUE_1 IS NULL or mse.VALUE_2 IS NULL THEN 100
            WHEN ( SELECT count(*) FROM cmc_mem_src cms WHERE cms.tn_id = mse.type_value ) = 0 THEN 222
            WHEN count(mse.value_1) over ( partition by type_value ) > 1 THEN 333
       ELSE '000' int_value_1  
FROM   mem_src_extn mse
) u
ON ( t.rowid = u.row_id )
WHEN MATCHED THEN UPDATE SET t.int_value_1 = u.int_value_1 WHERE u.inv_value_1 != '000';

MERGE statements have an added bonus for developers -- you can simply run the SELECT part of them to see what your results are going to be. That makes it easier to debug.

Also note, your original UPDATE statements would not update anything to 000, but your proposed solution would. So, it's not clear what your requirement there is. My solution does not update anything to 000, thanks to the WHERE clause in the UPDATE clause of the MERGE. You can remove that if you want to set rows not meeting any of the three conditions to 000.

Clubbing update statements using case in oracle

2 Answers2