Re: SQL: update column with a sequential #

Hi, Joel:

Well, you could do something like this:

Let's suppose the name of the table (file) you want to update is called "TEST" and the column (field) name to be updated is named "LNUM"

#1. run a RGZPFM to ensure you compress out any deleted records.

NOTE: if you need the records to be kept in a certain order by key, you can specify the KEYFILE parameter on the RGZPFM command.

#2. run the following SQL statement (e.g. using STRSQL):

update library/test set LNUM = RRN(test)

This will assign the "relative record number" for each row (record) to the LNUM column (field) for each row (record).

CAVEATS:

Whenever any program inserts any records, the program needs to update the LNUM column with the new RRN.

If any programs delete any records, you will need to re-run the procedure above to RGZPFM and then re-number the existing records. You might want to schedule this to run as a separate batch job, perhaps at night, if you can tolerate "skipping" over deleted records -- the LNUM may now have "gaps"... until you re-sequence everything.

Does that help?

Mark S. Waterbury

> On 4/30/2013 6:01 PM, Stone, Joel wrote:

Is it possible to update a column with a sequential # ?

So that for this particular column, each row has a value 1 higher than the prior row?

Something like

UPDATE filename set SEQCOL to prior SEQCOL + 1

(This is a DDS created file that does NOT have an identity column.)

Thanks!