Skip to content
Advertisement

How to update only first NULL column with a value in Snowflake sql?

How to update a table first Null column with value and other Null columns with the text ‘Available’?

My attempt: I tried using Case statements but it is affecting the performance of a query.

Update Emp
SET Level1 = (CASE WHEN Level1 IS NOT NULL THEN Level1 ELSE PermissionCode END), 
    Level2 = (CASE WHEN Level1 IS NOT NULL AND Level2 IS NULL THEN PermissionCode ELSE Level2  END),
  ..and so on

Is there any efficient way to update a table as mentioned below the expected format?

Current Table structure:
========================
| EmpID  | Level1 | Level2 | Level3 | Level4  |....| Level256 | PermissionCode  |
|--------|--------|--------|--------|---------|....|----------|-----------------|        
| 124RY7 | abc    | wsg    | NULL   | NULL    |....|    NULL  |    RT12345      |
| 5T7YTR | efg    | NULL   | NULL   | NULL    |....|    NULL  |    654GTY       |
 

Expected Output:

| EmpID  | Level1 | Level2 | Level3  | Level4  |....| Level256 | PermissionCode  |
|--------|--------|--------|---------|---------|....|----------|-----------------|        
| 124RY7 | abc    | wsg    |RT12345  |Available|....|Available |    RT12345      |
| 5T7YTR | efg    | 654GTY |Available|Available|....|Available |    654GTY       |

  

Advertisement

Answer

Try using a combination of nvl2 and coalesce

select empid, lvl1, lvl2, lvl3, lvl4, PC
from temp
union all
select empid, coalesce(lvl1,lvl2,lvl3,lvl4,PC), nvl2(lvl1, coalesce(lvl2,lvl3,lvl4,PC), 'Available'), nvl2(lvl2, coalesce(lvl3,lvl4,PC), 'Available'), nvl2(lvl3, coalesce(lvl4,PC), 'Available')
   , PC
from temp;

enter image description here

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement