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?
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')
;