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

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?

CodePudding user response：

You can use a case expression, for example:

update table 
set    id = case 
         when id in ('TEST_TEST1','TEST_TEST2' ) then concat('PREFIX',id) 
         when id ='TEST_TEST3' then 'PREFIX_TESTTEST3'
       end 
where  id in ('TEST_TEST1','TEST_TEST2','TEST_TEST3')

CodePudding user response：

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>

CodePudding user response：

You can also decode function to do that

update Your_table 
    set id = 'PREFIX_' || decode( id, 'TEST_TEST3', replace(id, '_', ''), id )
where id in ('TEST_TEST1', 'TEST_TEST2', 'TEST_TEST3') 
;