Re: Updating by row count in Interactive SQL

Mark Villa wrote:

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.

Couple of options sprint to mind. Unfortunately, the easiest doesn't work.

This WILL NOT work:

UPDATE thisTABLE
set RECCNT = (select max(RECCNT) from thisTABLE) + 1
where date1 > 20080000


The SQL engine is far smarter than mere mortals and caches the subselect, then applies it statically to reach record. You end up with all the updated records having the same value; what you need is the subselect to be re-run for each update. No such luck, and I have not found any option in SQL that will force this behavior.

So instead you might want to look at REXX. If I'm not mistaken, REXX is available even when standard development tools are not, and it allows you to intermingle SQL and CL instructions pretty nicely. See if you have STREXPRC, and if so, you're good to go. You can create a cursor, create a CL variable, add one to the variable each time you update a record in the cursor.

Joe