Fill null columns between not null columns with default value

Question

I am trying to fill all NULL columns between specified columns.

My Table:

I want to fill every column which is NULL if a higher level has a value in it.

For example:

Row with ID 1 Level3 should have a default value ('def')
Row with ID 2 is ok. Last filled Column is level4 and no column after that has a value in it.
Row with ID 3 Level5, Level6, Level7, Level8, Level9 should have a default value ('def')
Row with ID 4 Level4, Level5, Level6, Level8, Level9 should have a default value ('def')

SQL Fiddle

What if there's a value in Level1, Level2 and Level5 and Level7? Should Level6 be updated with a default value also? — Felix Pamittan
– Felix Pamittan, Commented Sep 16, 2015 at 6:05
sorry my fault. Yes every column which has NULL and has a higher column with a value in it. Ive edited my post ;-) — WhoisIt
– WhoisIt, Commented Sep 16, 2015 at 6:24
See this one based on@cha 's answer: sqlfiddle.com/#!3/032ba/2/0 — Felix Pamittan
– Felix Pamittan, Commented Sep 16, 2015 at 6:43

Felix Pamittan · Accepted Answer · 2015-09-16 06:57:06Z

3

This is the best I could think of:

update #Levels
set 
level1 = CASE WHEN COALESCE(level2, level3, level4, level5, level6, level7, level8, level9, level10) IS NULL THEN level1 ELSE ISNULL(level1, 'def') END,
level2 = CASE WHEN COALESCE(level3, level4, level5, level6, level7, level8, level9, level10) IS NULL THEN level2 ELSE ISNULL(level2, 'def') END,
level3 = CASE WHEN COALESCE(level4, level5, level6, level7, level8, level9, level10) IS NULL THEN level3 ELSE ISNULL(level3, 'def') END,
level4 = CASE WHEN COALESCE(level5, level6, level7, level8, level9, level10) IS NULL THEN level4 ELSE ISNULL(level4, 'def') END,
level5 = CASE WHEN COALESCE(level6, level7, level8, level9, level10) IS NULL THEN level5 ELSE ISNULL(level5, 'def') END,
level6 = CASE WHEN COALESCE(level7, level8, level9, level10) IS NULL THEN level6 ELSE ISNULL(level6, 'def') END,
level7 = CASE WHEN COALESCE(level8, level9, level10) IS NULL THEN level7 ELSE ISNULL(level7, 'def') END,
level8 = CASE WHEN COALESCE(level9, level10) IS NULL THEN level8 ELSE ISNULL(level8, 'def') END,
level9 = CASE WHEN COALESCE(level10, null) IS NULL THEN level9 ELSE ISNULL(level9, 'def') END

Looks messy, but does the job

SQL Fiddle

edited Sep 16, 2015 at 6:57

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

answered Sep 16, 2015 at 6:20

cha

10.4k1 gold badge20 silver badges27 bronze badges

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

4 Comments

Felix Pamittan Over a year ago

Replace the ELSE part with ISNULL(<column>, 'def') and you're good to go!

cha Over a year ago

Thanks @FelixPamittan. That was the missing link

Felix Pamittan Over a year ago

You could also add a WHERE Level1 IS NULL OR Level2 IS NULL ... At least it wouldn't have to update all rows with the WHERE clause.

WhoisIt Over a year ago

Thanks :-) My first attempts where all to complicated.. :-)

Collectives™ on Stack Overflow

Fill null columns between not null columns with default value

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related