Skip to content
Advertisement

how to use an if statement in update query in Oracle

I wanted to use if statement in Oracle update. Below is the condition that i wanted to achieve and i will not be able to use PL/SQL. I wanted to achieve the below result using update statement.

if code like'0001'  then
   code ='0001'
elsif code like'01' then 
   code = 'ok'
else 
   code = 'NOTOK'
end

I tried the below update statement not exactly what i need but just trying a simple update using case..

UPDATE tblname
SET CODE =  CASE
              WHEN CODE not like '01% OR CODE not like '0001% THEN
                'NOTOK'
            END

Advertisement

Answer

Seems you want to use

UPDATE tblname 
   SET "DESC" = CASE 
                WHEN SUBSTR(CODE,1,2)='01' THEN 
                     'OK' 
                WHEN SUBSTR(CODE,1,4)='0001' THEN 
                     SUBSTR(CODE,1,4)
                ELSE
                     'NOT OK'
                END

desc is a reserved keyword. So, there cannot be a column name unquoted.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement