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

Question

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       |

Are you using SQL Server? Or Snowflake SQL? I think maybe you need to remove the SQL Server tag. — Dale K
– Dale K, Commented Oct 22, 2020 at 23:39
This would be simpler if you fixed your data model so the different "level" columns were stored in rows and not columns. — Gordon Linoff
– Gordon Linoff, Commented Oct 23, 2020 at 0:10
I was originally thinking about doing an UNPIVOT but the NULLs seem to present a problem with that. — Daniel Zagales
– Daniel Zagales, Commented Oct 23, 2020 at 0:27

Daniel Zagales · Accepted Answer · 2020-10-23 00:12:12Z

2

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;

edited Oct 23, 2020 at 0:12

answered Oct 22, 2020 at 23:27

Daniel Zagales

3,0722 gold badges8 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

K.Tom Over a year ago

it works for First Null column and the remaining columns should be populated as 'Available'

Daniel Zagales Over a year ago

yup re-reading that now, missed over that the first time I read it.

Daniel Zagales Over a year ago

Updated, this works for me. You can likely get the SQL to generate quickly in excel.

MMV · Accepted Answer · 2020-10-23 08:32:54Z

0

the question is what the actual usecase is. What you can do is try a bulk update (just will have to get autocommit off and commit manually) like "set levelX=Available where levelX-1 is null" for each level for all the columns, which will eventually leave you with just 1 null in every row which you can then fill with the code.

answered Oct 23, 2020 at 8:32

MMV

9906 silver badges10 bronze badges

2 Comments

K.Tom Over a year ago

how to set level on columns?

MMV Over a year ago

you can try write a stored procedure building the queries if you want to automate and they really look like levelX, levelX+1 and so on

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related