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.
x
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.