how to use an if statement in update query in Oracle

Question

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

are you trying to update code or desc column ? OK logically should be desc — Barbaros Özhan
– Barbaros Özhan, Commented Nov 7, 2019 at 18:22
Well, Barbaros is right, that's quite confusing. In your pseudo code you have code, '001', 'ok', and 'NOTOK', and in your update statement there is DESC and 'DIRECT' instead. — Thorsten Kettner
– Thorsten Kettner, Commented Nov 7, 2019 at 18:26
Thanks Barbaros and Thorsten for looking into it, I have corrected it. — kumar007
– kumar007, Commented Nov 7, 2019 at 18:36

Thorsten Kettner · Accepted Answer · 2019-11-07 19:45:15Z

3

You can apply multiple WHEN ... THEN. The first match wins, so it works just like your if / else if.

UPDATE tblname 
SET code = CASE WHEN code LIKE '0001%' THEN '0001'
                WHEN code LIKE '01%' THEN 'ok'
                ELSE 'NOTOK'
           END;

edited Nov 7, 2019 at 19:45

answered Nov 7, 2019 at 18:16

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kumar007 Over a year ago

Thanks Thorsten, I just found and i tried and it worked too. Thanks for your support.

Barbaros Özhan · Accepted Answer · 2019-11-07 18:31:09Z

0

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.

edited Nov 7, 2019 at 18:31

answered Nov 7, 2019 at 18:17

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

1 Comment

kumar007 Over a year ago

Thank you so much Barbaros. I was struggling to find the solution for 2 hours..

Collectives™ on Stack Overflow

how to use an if statement in update query in Oracle

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related