OraFAQ Forum: SQL & PL/SQL » update with case using select

Home » SQL & PL/SQL » SQL & PL/SQL » update with case using select (11.2.0.3.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

update with case using select [message #599926]

Wed, 30 October 2013 04:44

lijok
Messages: 68
Registered: April 2005

Member

Can we use select statement in case statment for an update?
Example

UPDATE x SET field = CASE WHEN
    (SELECT field1
    FROM Y
    WHERE field3 = x.field3
    IS NOT NULL
    THEN 'B'
    ELSE 'C'
where cny# = 112160

Report message to a moderator

Re: update with case using select [message #599927 is a reply to message #599926]

Wed, 30 October 2013 04:46

lijok
Messages: 68
Registered: April 2005

Member

correcting on closing bracket..

UPDATE x SET field = CASE WHEN
    (SELECT field1
    FROM Y
    WHERE field3 = x.field3)
    IS NOT NULL
    THEN 'B'
    ELSE 'C'

Report message to a moderator

Re: update with case using select [message #599930 is a reply to message #599927]

Wed, 30 October 2013 05:00

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes you can.

SQL> update t 
  2  set v = case when (select 1 from dual) is not null then 1 else 2 end;

3 rows updated.

Why didn't you simply try?

Report message to a moderator

Re: update with case using select [message #599941 is a reply to message #599930]

Wed, 30 October 2013 05:43

lijok
Messages: 68
Registered: April 2005

Member

I tried, but failed, I was missing the END
Thanks for your help!

Report message to a moderator

Previous Topic:	Pagination Query
Next Topic:	Troubleshoot procedure error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 23:08:07 CDT 2024