Update with Case and in - Oracle

UPDATE with CASE and IN - Oracle

Got a solution that runs. Don't know if it is optimal though. What I do is to split the string according to http://blogs.oracle.com/aramamoo/2010/05/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement.html

Using:

select regexp_substr(' 1, 2 , 3 ','[^,]+', 1, level) from dual

connect by regexp_substr('1 , 2 , 3 ', '[^,]+', 1, level) is not null;

So my final code looks like this ($bp_gr1' are strings like 1,2,3):

UPDATE TAB1
SET    BUDGPOST_GR1 =
          CASE
             WHEN ( BUDGPOST IN (SELECT     REGEXP_SUBSTR ( '$BP_GR1',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                 FROM       DUAL
                                 CONNECT BY REGEXP_SUBSTR ( '$BP_GR1',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                               IS NOT NULL) )
             THEN
                'BP_GR1'
             WHEN ( BUDGPOST IN (SELECT     REGEXP_SUBSTR ( ' $BP_GR2',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                 FROM       DUAL
                                 CONNECT BY REGEXP_SUBSTR ( '$BP_GR2',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                               IS NOT NULL) )
             THEN
                'BP_GR2'
             WHEN ( BUDGPOST IN (SELECT     REGEXP_SUBSTR ( ' $BP_GR3',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                 FROM       DUAL
                                 CONNECT BY REGEXP_SUBSTR ( '$BP_GR3',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                               IS NOT NULL) )
             THEN
                'BP_GR3'
             WHEN ( BUDGPOST IN (SELECT     REGEXP_SUBSTR ( '$BP_GR4',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                 FROM       DUAL
                                 CONNECT BY REGEXP_SUBSTR ( '$BP_GR4',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                               IS NOT NULL) )
             THEN
                'BP_GR4'
             ELSE
                'SAKNAR BUDGETGRUPP'
          END;

Is there a way to make it run faster?

How to use Update command with CASE statement in Oracle?

You about using exists?

UPDATE F_STATE_MAPPING
    SET MATCHING_FLAG = (CASE WHEN EXISTS (SELECT 1
                                           FROM TEMP_STN_STATE_MAPPING
                                           WHERE F_ZN = IC_ZN AND F_STN = IC_STN
                                          )
                              THEN 1 ELSE 0
                         END) ;

Update multiple rows using CASE WHEN - ORACLE

Ok based on the fiddle you have given i have tried these and it worked for me

create table account(  account_id number primary key,
                           account_status varchar2(30));

insert into account values(1, '5');
insert into account values(2, '3');
insert into account values(3, '2');

select * from account

update account
set account_status= case
when account_id=1 then '2'
when account_id=2 then '5'
when account_id=3 then '3'
END

select * from account

I didn't use the where condition

Oracle SQL- Update Query with Case Statement Missing Keyword Error

CASE returns a value so you don't need the SET there. AND is a conditional operator, it's not for running commands together.

REPLACE() does not work the way you're trying to use it. It takes literals not wildcards. So unless value starts and finishes with underscores, '_%' the command won't change anything. Instead of REPLACE() I suggest you use SUBSTR() and concatenate the replacement characters.

UPDATE t1  
SET value = 
    CASE   WHEN value LIKE '%a%' THEN UPPER(value)   
           WHEN value LIKE '%d%' AND value NOT LIKE '%a' THEN LOWER(value)   
           ELSE '1'||substr(value, 2, length(value)-2) ||'2' 
    END
Where value IS NOT NULL;

Alternatively, you could use the regular expression replace function

ELSE regexp_replace(value, '^(.)(.*)(.)$', '1\22' 

However, this has slightly different output when the length of value is 1.

Oracle - Update case when then else part do nothing

You can restrict the update just for rows with age 1 or 4

update details 
  set age= case 
            when age=4 then 1 
            when age=1 then 4 
           end
where age in (1,4)

This will work as well but you will do unnecessary updates

update details 
set age= case 
           when age=4 then 1 
           when age=1 then 4 
           else age 
          end

updating variables in oracle Case statement

Here is how you can get the result you want. Note that there are no variables involved. SQL is a declarative language: your code describes only the result you want, the code is not made up of specific instructions telling the computer HOW to get the result you want.

I am using the EMP table in the standard SCOTT schema; you can adapt for your needs.

select   empno, ename, job,
         count(case when job = 'MANAGER' then 1 end) over (order by empno) mgr_running_ct
from     scott.emp
order by empno
;

     EMPNO ENAME      JOB       MGR_RUNNING_CT
---------- ---------- --------- --------------
      7369 SMITH      CLERK                  0
      7499 ALLEN      SALESMAN               0
      7521 WARD       SALESMAN               0
      7566 JONES      MANAGER                1
      7654 MARTIN     SALESMAN               1
      7698 BLAKE      MANAGER                2
      7782 CLARK      MANAGER                3
      7788 SCOTT      ANALYST                3
      7839 KING       PRESIDENT              3
      7844 TURNER     SALESMAN               3
      7876 ADAMS      CLERK                  3
      7900 JAMES      CLERK                  3
      7902 FORD       ANALYST                3
      7934 MILLER     CLERK                  3

Understanding UPDATE with CASE statement in ORACLE evaluating all rows

You may add logic to your WHERE clause which also restricts the values of the EMPID:

UPDATE CLOSED_AREA_ACCESS
SET CURRENT_MONITOR = 
CASE
    WHEN EMPID = '00001' THEN NULL
    WHEN EMPID = '00003' THEN 1
END
WHERE
    LOGGEDIN = 1 AND
    EMPID IN ('00001', '00003');

Another approach would be to add an ELSE condition to the CASE expression which effectively no-ops and just reassigns the CURRENT_MONITOR to itself:

UPDATE CLOSED_AREA_ACCESS
SET CURRENT_MONITOR = 
CASE
    WHEN EMPID = '00001' THEN NULL
    WHEN EMPID = '00003' THEN 1
    ELSE CURRENT_MONITOR
END
WHERE LOGGEDIN = 1;

---

Related Topics