Re: Updating by row count in Interactive SQL

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.