Case SQL statement with updates
Hi Everyone,
Needed some help with a case statement and the update clause
Is it possible to run an update as part of an case when statement. On a database refresh we are trying to change destination location from prod to stage or dev depending on the database being created. (For eg. if Database being created is STAGE, then we want the destination to become /stage instead of /prod and if the database is anything else we want the destination to become /dev instead of /prod)
select name
case
when name ='STAGE' Then
update TABLEA set destination = '/stage' WHERE destination = '/prod';
Else
update TABLEA set destination = '/dev' WHERE destination = '/prod';
end
from v$database
If there is a better alternate way, please recommend as well. I have to get this done with sql/plsql.
Thank you for your assistance.