Re: sub-select in SQL

["hockchai Lim" <lim.hock-chai@xxxxxxxxxxxxxxx>]

BL1025INP file contains INSEQ# and INERRFLG fields.  This file can contains 
many records with same INSEQ# value.  For each group of INSEQ# value, if I 
found a record contains value of Y in INERRFLG, I must set INERRFLG to Y for 
all the rest of the records that have the same INSEQ# value.


"Alan Campin" <alan0307d@xxxxxxxxx> wrote in message 
news:mailman.31877.1299091198.2702.midrange-l@xxxxxxxxxxxx...
> Am I missing something. You are subquerying the same file so a simple 
> Where
> inerrFlg = 'Y' in the update statement should do it.
>
> On Wed, Mar 2, 2011 at 11:20 AM, Dennis Lovelady 
> <iseries@xxxxxxxxxxxx>wrote:
>
> > Yes, someone knows.  :)  The subquery is performed only once (before the
> > outer query is performed).
> > --
> > ... CAUTION!!! Do NOT look at laser with remaining eye.
> >
> > -----Original Message-----
> > From: hockchai Lim <lim.hock-chai@xxxxxxxxxxxxxxx>
> > Reply-to: Midrange Systems Technical Discussion
> > <midrange-l@xxxxxxxxxxxx>
> > To: midrange-l@xxxxxxxxxxxx
> > Subject: sub-select in SQL
> > Date: Wed, 2 Mar 2011 12:02:25 -0600
> > Newsgroups: midrange.midrange-l
> >
> > For the sql below, does any one know if the sub-select in the where
> > statement gets execute only once or it gets execute for each row in
> > BL1025INP file?
> >
> > thanks
> >
> > update
> >   BL1025INP a
> >  set
> >   a.INERRFLG = 'Y'
> >  where
> >   a.inseq# in (
> >    select inseq#
> >     from BL1025INP b
> >     where
> >       b.inerrFlg = 'Y'
> >     group by
> >       INSEQ#)
> >
> >
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> > list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.