使用CASE WHEN-ORACLE更新多行 [英] Update multiple rows using CASE WHEN - ORACLE

问题描述

我的结构表ACCOUNT如下:

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

我需要使用一个SELECT语句一次更新三行，以使第二列分别为5、3、2.
我使用以下查询，但似乎缺少某些内容

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')  

我的问题，这样正确吗?如果不是，是否可以使用CASE WHEN语句以及如何或只能选择使用SUB-SELECT来在一条语句中实现这一点?
请注意，这是针对SQL ORACLE

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

推荐答案

基于您提供的小提琴，我已经尝试了这些，并且对我有用

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

我没有使用where条件

I didn't use the where condition

这篇关于使用CASE WHEN-ORACLE更新多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！