Updating the table using wildcards in the Switch case statement

I am trying to use an Oracle SQL update statement using a ‘SWITCHED’ case statement as mentioned below:

update MY_TABLE SET STATE_ABBREVIATION=
(CASE STATE
WHEN 'MAHARASHTRA' THEN 'MH'
WHEN 'ASSAM' THEN 'AS'
WHEN 'HIMACHAL%' THEN 'HP'
...
ELSE
STATE
END
);

I got this clue from the thread SQL – WHERE clause on each SET command in UPDATE?

The issue is that the wildcard fields are not functioning as expected. The State column may values like ‘Himachal Pradesh’ as well as ‘HimachalPradesh’.

I also searched https://community.oracle.com/thread/290198
,one of the answers there used a ‘ searched
case’ which works fine.. ..

CASE
(WHEN STATE LIKE 'ANDHRA%' THEN 'AP'
 WHEN STATE LIKE 'HIMACHAL%' THEN 'HP'
END

How can we achieve this in the SWITCHED case style ? I also tried

update MY_TABLE SET STATE_ABBREVIATION=
(CASE STATE
WHEN 'MAHARASHTRA' THEN 'MH'
WHEN 'ASSAM' THEN 'AS'
WHEN LIKE 'HIMACHAL%' THEN 'HP'
...
ELSE
STATE
END
);

This too did not work.

(SQL Error: ORA-00905: missing keyword 00905. 00000 - "missing keyword").
Hello, buddy!责编内容来自:Hello, buddy! (源链) | 更多关于

阅读提示:酷辣虫无法对本内容的真实性提供任何保证,请自行验证并承担相关的风险与后果!
本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Updating the table using wildcards in the Switch case statement

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录