CODEWITHSUNDEEP
sqloracle-databaseplsqlcase-when
CompEng
CompEng

Reputation: 7376

ORA-00936: missing expression. why I get this message?

I mean I try everything using begin and end but it doesnt work. How can I fix this?

select
     case 
    when exists
    (select ORG_UNIT_NAME from aaa.bbb 

    where ORG_UNIT_NAME ='ccc' and created_Date=to_date('01/11/2012','dd/mm/yyyy') )

    then

    update  aaa.bbb 
    set PROJECT_QUOTA=555 where ORG_UNIT_NAME  ='ccc' and created_Date=to_date('01/11/2012','dd/mm/yyyy')


    else
    'asd'


    end as exist_
    from dual

If I use 'ddd' after then instead of update it s work but why update isnt working.

Upvotes: 0

Views: 696

Answers (2)

Nick.Mc
Nick.Mc

Reputation: 19184

To only update based on a condition, just add it to the WHERE clause. But guess what - you already had it in there. This query should do what you want - does it?

update  aaa.bbb 
set PROJECT_QUOTA=555 
where ORG_UNIT_NAME  ='ccc' and created_Date=to_date('01/11/2012','dd/mm/yyyy')

Upvotes: 0

Álvaro González
Álvaro González

Reputation: 146410

In SQL, the CASE construct is not a flow control structure. Think of it of a generalised version of COALESCE(). You can't fit a full UPDATE query inside!

You probably want your main query to be of UPDATE type rather than SELECT. You can find some complex examples at http://psoug.org/reference/update.html

Upvotes: 1

Related Questions