Dynamically update null columns with the target values

December 29, 2022

Need to update columns which have null in column, with the target values

COL3 - DATE DATATYPE
COL1,2,4 - NUMBER DATATYPE

EXAMPLE

TABLE
COL1  COL2   COL3        COL4
1      2      29-02-22   NULL    
1      NULL   29-02-22   4    
2      4      29-02-22   8               
3      NULL   NULL       55    
4      5      29-02-22   NULL       
5      5      29-02-22   44        
5      6      29-02-22   4        
5      NULL   NULL       NULL

OUTPUT   

COL1  COL2   COL3     COL4
1      2   29-02-22    30     
1      3   29-02-22    4    
2      4   29-02-22    8               
3      5   29-02-22    55    
4      5   29-02-22    33       
5      5   29-02-22    44        
5      6   29-02-22    4        
5      1   29-02-22    2

How to compare null and replace it with the value. Tried Decode and Case in the update statement, however, Haven’t got any solution. The main purpose is to update the null value with the target value. The example illustrates to show what will be the source and how output will looks like

>Solution :

You can use the COALESCE function. COALESCE returns the first non-null argument in the list.

UPDATE myTable
SET
   Col1 = COALESCE(Col1, col1_targetValue),
   Col2 = COALESCE(Col2, col2_targetValue),
   Col3 = COALESCE(Col3, col3_targetValue),
   Col4 = COALESCE(Col4, col4_targetValue)

This will replace the NULL values in all rows. Add an appropriate WHERE clause to update only specific rows.