Multiple conditional updates in a single sql query PLSQL

Question

I have some values like this in the database with three records

id
TEST_TEST1
TEST_TEST2
TEST_TEST3

Now i need to append all the values with a "PREFIX". So it becomes PREFIX_TEST_TEST1, PREFIX_TEST_TEST2 etc. But for the third value TEST_TEST3, I have to change it to PREFIX_TESTTEST3 (no underscore)

So i made it using a two update queries like below

update table set id=concat('PREFIX',id) where id in ('TEST_TEST1','TEST_TEST2');

and the second update statement update table set id='PREFIX_TESTTEST3' where id='TEST_TEST3'

Is there any way we can make both these updates in one update statement?

Answer 1

CASE expression helps.

SQL> update test set
       id = 'PREFIX_' || case when id = 'TEST_TEST3' then replace(id, '_')
                              else id
                         end 
     where id in ('TEST_TEST1','TEST_TEST2','TEST_TEST3');

3 rows updated.

SQL> select * From test;

ID
------------------------------
PREFIX_TEST_TEST1
PREFIX_TEST_TEST2
PREFIX_TESTTEST3

SQL>