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.
x
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;