Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle - Is it possible to "set" values inside case statement during update as below?

Tags:

oracle

Is it possible to "set" values inside case statement during update as 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;
like image 586
Tinman Avatar asked Dec 19 '22 06:12

Tinman


2 Answers

The corresponding valid syntax would be like this.

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);
like image 171
Maheswaran Ravisankar Avatar answered May 13 '23 18:05

Maheswaran Ravisankar


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

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 could run a single update after the merge to change all non matching rows with 0.

like image 24
tbone Avatar answered May 13 '23 20:05

tbone