Re: update with multi table join

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Fri, 07 Nov 2003 05:50:37 GMT
Message-ID: <NoGqb.8068$1C5.5408@news02.roc.ny>

"mcstock" <mcstockspamplug_at_spamdamenquery.com> wrote in message news:CfqdndvBEplYWzeiRVn-tw_at_comcast.com...
> a) add a redundant where exists clause
> b) wrap the subquery in an NVL() function, i.e.:
>
> update dept
> set
> deptno = nvl(
> (select deptno from dept where dname = 'not')
> ,deptno
> )
> where deptno = 44
>
> btw: does anybody know if the use of a subquery in a function is is
> documented?
>
> --
> Mark C. Stock
> email mcstock -> enquery(dot)com
> www.enquery.com
> (888) 512-2048
>

--snip--

Not sure if its explicitly mentioned that this can be done .. but yes... You can now pretty much put sql expressions just about anywhere :)

Just for fun:

SQL> select
  2 decode ( (select 'x' from dual), (select 'x' from dual), (select 'y' from dual) )   3 from (select 'm' from dual)
  4 where (select 1 from dual) = (select 1 from dual)

  5      and (select 2 from dual) between (select 1 from dual) and (select 3 from dual)
  6      and nvl ( ( select null from dual ), (select 'z' from dual) ) = (select 'z' from dual)

  7 /

DECODE((SELECT'X'FROMDUAL),(SE

---

y

:)
Anurag Received on Thu Nov 06 2003 - 23:50:37 CST