how to update multiple column with condition in a single sql query

Question

I want to update multiple column with multiple condition. for.eg.

update student set name='john' where id=10 
update student set name='doe' where id=5 

How to update this in a single statement?

Answer 1

Use CASE WHEN

update student 
set name= CASE WHEN id = 5 THEN 'john'
               WHEN id = 10 THEN 'doe'
               ELSE name 
           END
where id in (
    5, 10
)

Answer 2

update tablename 
set coloumn_name1= CASE WHEN coloumn_name = 5 THEN 'john'
               WHEN coloumn_name = 10 THEN 'doe'
               ELSE name 
           END
where coloumn_name in (
    5, 10
)