Update Multiple Rows Using Case When - Oracle

Oracle sql, update multiple rows using CASE

Don't use case. Instead:

UPDATE GRIDCOLUMNS  
    SET TYPE = 6
    WHERE Fieldname = 'AccountNum' AND ID IN (19337, 19339, 19380, 19419);

This is much more efficient. If you really need to use CASE, then you need an ELSE:

UPDATE GRIDCOLUMNS  
    SET TYPE = (CASE WHEN Fieldname = 'AccountNum' AND ID IN (19337, 19339, 19380, 19419) THEN 6
                     ELSE TYPE
                END);

Otherwise, the non-matching rows get set to NULL. But really, use the WHERE.

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

PLSQL Update multiple rows with multiple sets

You could use a CASE expression

update t1
   set check = case loc when '020201' then '10000'
                        when '020301' then '10100'
                        ... etc.
                        end
 where loc in ('020201','020301',...);

But frankly I would just write several singleton update statements:

update t1 set check = '10000' where loc = '020201';
update t1 set check = '10100' where loc = '020301';
.etc.

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?

Oracle Sql update multiple records at once?

You could use CASE:

update printers 
set status = CASE WHEN printer ip = '1.1.1.1' THEN true ELSE false END
    ,row_update_date = SYSDATE
where printer id = 'A' 

Oracle - How to update multiple rows with different values in the same query?

you need get single row from BookSupplier for each bookNo in order to join with the Library table.
As you mentioned that you can use any supplier, we can try using max/min aggregate funtion.

merge into Library lib 
using ( select bookno,max(SupplierNo) as SupplierNo from BookSupplier
group by bookno ) bs
on
( lib.BookId = bs.BookNo
  and lib.BookShelfNo in ('4545','4546','4550'))
when matched then
update set lib.BookSupplierNo = bs.SupplierNo

above will give you for each bookno , max SupplierNo and then you can join it with Library table on bookid and update the records accordingly.

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