Re: sub-select in SQL

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.