Re: UPDATE with DECODE in subquery

That was a good "catch" on your part, and the SQL used to have that additional where clause for the UPDATE statement. However, I gave you a somewhat stripped down version of the UPDATE. I am actually updating (4) columns with the same update, and the other three must be updated regardless of the PROJ_DESC value.

Either way, I tested the embedded SELECT statement by trying to UPDATE where proj_no = "MyProj"...so I would get an exact match. The update results still did not match the results of running the embedded SELECT alone.

I think I have a bug.

Thanks,

Brian

In article <01bee6a7$b4fa9300$dee179c3_at_nentjesi>,   "Wolf 'n Pinguin" <duif.op.spijkers_at_wxs.nl> wrote:
>
>
> vick9935_at_my-deja.com schreef in artikel
<7oupi4$8uk$1_at_nnrp1.deja.com>...
>
> <SNIP>
>
> > Here is the UPDATE statement with subquery:
> >
> > UPDATE CP_PROJECT a
> > SET PROJ_DESC =
> > (SELECT
> >

DECODE(a.PROJ_DESC,NULL,DECODE(b.PROJ_DESC,NULL,c.SITE_NAME,b.PROJ_DESC)
> > ,a.PROJ_DESC)
> > FROM CP_SITE c,CP_ORACLE_PROJECT b
> > WHERE a.PROJ_NO = c.PROJ_NO(+)
> > AND a.PROJ_NO = b.PROJ_NO(+)
> > AND c.SITE_STATUS(+) = 'P'
> > )
> > ;
>
> Hi,
>
> I don't see anything wromng at face value, but
> why don't you simply add "WHERE PROJ_DESC is not NULL" to the update
(i.e.
> autside the subquery) ? The known project names will be left alone.
>
> Good Luck, Ide
>
>

--
Brian in Seattle
Pride, commitment, teamwork - words we use to get you to work for free.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Aug 16 1999 - 09:59:07 CDT