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;