Update multiple rows using CASE WHEN - ORACLE

Question

I have the table ACCOUNT of structure as follow:

ACCOUNT_ID  | ACCOUNT_STATUS| 
004460721   |      2        | 
042056291   |      5        | 
601272065   |      3        |

I need to update the three rows at once using one SELECT statement such that, the second column will be 5, 3, 2 respectively.
I used the following query but seems there is something missing

UPDATE ACCOUNT
SET ACCOUNT_STATUS = CASE   
WHEN ACCOUNT_STATUS = '004460721' THEN 5  
WHEN ACCOUNT_STATUS = '042056291' THEN 3  
WHEN ACCOUNT_STATUS = '601272065' THEN 2  
WHERE ACCOUNT_ID IN ('004460721','042056291','601272065')

My question, is this way correct? if no, can I use CASE WHEN statement and how or I only have choice of using SUB-SELECT to acheive that in one statement?
Kindly, notice this is for SQL ORACLE

What kind of update are you trying to do? Set them all to the same value? — Joachim Isaksson, Sep 12 '13 at 09:39
@hawk did you tried schurik answer below. I think that might work... — Sarathi Kamaraj, Sep 12 '13 at 10:09
Go to this link- http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/update_statement.htm, read it thoroughly, make sure you understand every bit of it and then either edit or close this question. — Rachcha, Sep 12 '13 at 10:10
@Joachim Isaksson it is updated now, sorry for the confusion — Hawk, Sep 12 '13 at 10:19
@SarathiKamaraj Yes, something is wrong, you can check the link I posted in the comment below. — Hawk, Sep 12 '13 at 10:27
@Rachcha I read it thoroughly and I found nothing about CASE WHEN, some posts here uses it in ORACLE which confuse me http://stackoverflow.com/questions/5170864/update-with-case-and-in-oracle — Hawk, Sep 12 '13 at 10:43

score 8 · Accepted Answer · edited Sep 17 '15 at 16:43

8

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

edited Sep 17 '15 at 16:43

WorkSmarter

3,738
3
29
34

answered Sep 12 '13 at 10:23

Sarathi Kamaraj

647
3
9
26

@hawk yeah it is not working in fiddle.. I tried in my oracle apex apex.oracle.com it worked fine for me – Sarathi Kamaraj Sep 12 '13 at 11:14
Correct, I found the problem with Fiddle, I should run `select` with `update`, if I run them separately Fiddle will rollback. Thanks – Hawk Sep 12 '13 at 11:43

score 4 · Answer 2 · answered Sep 12 '13 at 09:53

try the following

update account
set account_status = CASE account_id 
                     WHEN 004460721 then 2 
                     WHEN 042056291 THEN 5 
                     WHEN 601272065 THEN 3 
                     END
WHERE account_id IN (004460721, 042056291, 601272065 )
;

score 3 · Answer 3 · answered Mar 13 '14 at 16:36

3

Though bit late I would like to add one point to the above answers. The Where condition is important because with out that, the query will update all rows.

answered Mar 13 '14 at 16:36

Dexter

4,036
3
47
55

score 1 · Answer 4 · answered Sep 12 '14 at 18:28

1

how about changing

WHEN ACCOUNT_STATUS = '004460721'

to

WHEN ACCOUNT_ID = '004460721'

etc. in the original code

answered Sep 12 '14 at 18:28

AnnoyedUser

11
3

Update multiple rows using CASE WHEN - ORACLE

4 Answers4

Linked