Re: Updating by row count in Interactive SQL

[R Bruce Hoffman <bruce.hoffman@xxxxxxxxxxxxxxxxx>]

I get the impression you want to actually number the resulting records. 
And you said from interactive SQL.
You could do it in multiple statements:

Create a duplicate...
create table dingnum like ding                           
Table DINGNUM created in HOFFMANB.                      

Number over any selection you have and insert into duplicate...
insert into dingnum                                      
   (select row_number() over() as row, a from ding where .... )      
6 rows inserted in DINGNUM in HOFFMANB.                 

Update back to original table, you would need some form of record 
identifier that uniquely specified the records... using a correlated 
subquery....
update ding a set a.i = (select i from dingnum b         
  where a.a = b.a)                                       
6 rows updated in DING in HOFFMANB.                     

That what you are looking for? This would have the effect of numbering, 
in this case, from 1 to 6, each of the selected records in the original 
table.
Lotta ifs in there though...


Elvis Budimlic wrote:
> Mark, 
>
> I like Charles' train of thought, but after reading your post I'm thinking
> something as simple as this might work for you:
>
> update thisTABLE
> set RECCNT = RECCNT + 1   
> where date1 > 20080000
>
> All that example above does is bump RECCNT by 1 for every record that
> matches your selection criteria (WHERE clause).
> Would that suffice?
> If not, can you give another example of existing RECCNT value and what you'd
> like to set it to?
>
> Elvis
>
> Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
> www.centerfieldtechnology.com
>  
>
> -----Original Message-----
> Subject: Updating by row count in Interactive SQL
>
> Hi all,
> Any suggestions on how you would do this in interactive SQL?
>
> UPDATE thisTABLE
> counter = counter + 1
> set RECCNT = counter
> where date1 > 20080000
>
> An example would be most appreciated. The above, obviously does not work.
>
> The only two options I have in this case are SQL and UPDDTA record by
> record.
>
> BTW, every now and then I see answers posted asking a good question -
> why would you want to do that? - the hard way? I suspect it is usually
> because we are working on a box with restrictions, no developer
> environment, etc.
>
> When we are in our native environment these questions (with simple
> answers in another tool) don't pop up.
>
>