Re: Update the first 1500 records that need updating

[Booth Martin <booth@xxxxxxxxxxxx>]

I feel so special....



SJL wrote:
> Sue -
> I was sooooo close...
>
> Questions:
>
> 1) Is the 'derived' table analagous to a table created by a CTE?  I tried using a CTE to 
> no avail...
>
> 2) I suppose that X below is the name of the 'derived' table?
>
> SELECT R FROM
>   (
>    SELECT RRN(F) R from FILEA F where FIELDA='X'
>       Fetch first 1500 rows only
>    ) X
>
> 3) Where would you find documentation for this in the System i online reference books?
>
>
> - sjl
>
> ----- Original Message ----- 
> From: "Sue Romano" <slromano@xxxxxxxxxx>
> To: <rpg400-l@xxxxxxxxxxxx>
> Sent: Wednesday, December 05, 2007 1:24 PM
> Subject: RE: Update the first 1500 records that need updating
>
>
>   
> > FETCH FIRST N ROWS is only supported in the outer level of a query and in a
> > derived table, not in a subquery (which is where you are trying to use it).
> > What you can do is push the clause one level deeper into a derived table.
> >
> > UPDATE FILEA F
> > SET FIELDA = 'N' where RRN(F) in
> > ( SELECT R FROM
> >  (
> >   SELECT RRN(F) R from FILEA F where FIELDA='X'
> >      Fetch first 1500 rows only
> >   ) X
> > )
> >
> > This has been supported since V5R2.
> >
> > Sue Romano
> > i5/OS SQL parser
> >
> >     
>
>