Oracle Is it possible to "set" the values ​​inside the case statement during an update, as shown below?

Question

Is it possible to "set" the values ​​inside the case statement during an update, as shown below?

UPDATE TABLE1
  CASE WHEN COL1 = 'A' THEN SET COL2 = 10, COL3 = 20, COL4 = 30
       WHEN COL1 IN ('B','N') THEN SET COL2 = 1, COL3 = 5, COL4 = 7
       WHEN COL1 = 'D' THEN SET COL2 = 11, COL3 = 13, COL4 = 17
       ELSE SET COL2 = 0, COL3 = 0, COL4 = 0
  END;

Answer 1

The corresponding valid syntax will be as follows.

UPDATE TABLE1 SET
  COL2 = (CASE WHEN COL1 = 'A' THEN 10
               WHEN COL1 IN ('B','N') THEN 1
               WHEN COL1 = 'D' THEN 11
               ELSE 0
          END), 
  COL3 = (CASE WHEN COL1 = 'A' THEN 20
               WHEN COL1 IN ('B','N') THEN 5
               WHEN COL1 = 'D' THEN 13
               ELSE 0
          END), 
  COL4 = (CASE WHEN COL1 = 'A' THEN 30
               WHEN COL1 IN ('B','N') THEN 7
               WHEN COL1 = 'D' THEN 17
               ELSE 0
          END);

Answer 2

To do this using case, you will need to repeat casefor each field (as @MaheswaranRavisankar shown). It only works case. An alternative would be to create a subquery that gives the same results. While it is longer, it groups related values ​​together, which may be more readable / easier to maintain.

, , , .

UPDATE table1
SET    col2 = 0, col3 = 0, col4 = 0;

UPDATE table1
SET    (col2, col3, col4) =
          (SELECT a.col2, a.col3, a.col4
           FROM   (SELECT 'A' AS col1,
                          10 AS col2,
                          20 AS col3,
                          30 AS col4
                   FROM   DUAL
                   UNION ALL
                   SELECT 'B' AS col1,
                          5 AS col2,
                          5 AS col3,
                          7 AS col4
                   FROM   DUAL
                   UNION ALL
                   SELECT 'N' AS col1,
                          5 AS col2,
                          5 AS col3,
                          7 AS col4
                   FROM   DUAL
                   UNION ALL
                   SELECT 'D' AS col1,
                          13 AS col2,
                          7 AS col3,
                          17 AS col4
                   FROM   DUAL) a
           WHERE  a.col1 = table1.col1);

: , , SQL.

Answer 3

It looks like you are trying to do MERGE, with one exception. You can update the table in a single merge statement as follows, except for your logic, to update all non-matching rows to 0.

SQL> create table tab1
(
col1 varchar2(10),
col2 number,
col3 number,
col4 number,
merge_flag char(1)
)
Table created.
SQL> insert into tab1 values ('A', 10,11,12,null)
1 row created.
SQL> insert into tab1 values ('B', 20,21,22,null)
1 row created.
SQL> insert into tab1 values ('C', 30,31,32,null)
1 row created.
SQL> commit
Commit complete.
SQL> select * from tab1

COL1             COL2       COL3       COL4 MERGE_FLAG
---------- ---------- ---------- ---------- ----------
A                  10         11         12           
B                  20         21         22           
C                  30         31         32           

3 rows selected.
SQL> merge into tab1 t
using (
    select 'A' as col1, 10 as col2, 20 as col3, 30 as col4 from dual
    union
    select 'B' as col1, 1 as col2, 5 as col3, 7 as col4 from dual
    union
    select 'N' as col1, 1 as col2, 5 as col3, 7 as col4 from dual
    union
    select 'D' as col1, 11 as col2, 13 as col3, 17 as col4 from dual
) x
on (t.col1 = x.col1)
when matched then
    update set t.col2 = x.col2, t.col3 = x.col3, t.col4 = x.col4, t.merge_flag = 'X'
Merge successfully completed.
SQL> commit
Commit complete.
SQL> select * from tab1

COL1             COL2       COL3       COL4 MERGE_FLAG
---------- ---------- ---------- ---------- ----------
A                  10         20         30 X         
B                   1          5          7 X         
C                  30         31         32           

3 rows selected.

You can run one update after a merge to change all non-matching rows from 0.