There are certain situation where we need some updation on the basis of condition. For example if name contain ‘Kruthika’ update it to ‘Kruthik’.
Our table is containing the following records
select * from user_tab
user_id user_name
active
-----------
-------------------------------------------------- ------
1 Kruthika 1
2 Orpita 1
3 Aalap 1
4 Trishona 1
5 Shobna 1
6 Eekalabya 1
7 Phaneesh 1
8 Kishalaya 1
9 Mrinmoy 1
10 Satyajit 1
16 suranjan 1
(11 row(s) affected)
You might be thinking this update can be accomplished with
the help of were condition in update statement. Correct it goes like this
update user_tab
set user_name = 'Kruthik'
where user_name = 'Kruthika'
(1 row(s) affected)
select * from user_tab
user_id
user_name active
-----------
-------------------------------------------------- ------
1 Kruthik 1
2 Orpita 1
3 Aalap 1
4 Trishona 1
5 Shobna 1
6 Eekalabya 1
7 Phaneesh 1
8 Kishalaya 1
9 Mrinmoy 1
10 Satyajit 1
16 suranjan 1
(11 row(s) affected)
But consider a situation
where you need to update ‘Trishona’ with
‘Trishon’ and ‘suranjan’ with ‘suranjana’.
Is it possible with WHERE clause in Update statement. ANSWER
IS NO.
Then what is option left for such update, Here comes CASE, let see how this update can be
done with CASE
update user_tab
set user_name = (case user_name
when 'Trishona' then 'Trishon'
when 'suranjan' then 'suranjana'
else user_name end)
user_id user_name
active
-----------
-------------------------------------------------- ------
1 Kruthika 1
2 Orpita 1
3 Aalap 1
4 Trishon 1
5 Shobna 1
6 Eekalabya 1
7 Phaneesh 1
8 Kishalaya 1
9 Mrinmoy 1
10 Satyajit 1
16 suranjana 1
(11 row(s) affected)
I really like this keyword a lot of thing can be
accomplished with CASE.
Post Reference: Vikram Aristocratic Elfin Share
No comments:
Post a Comment