db2 – Increment values in a field sequentially

My task consisted of the following: Three fields in a table of a thousand records were blank. Some tuples/rows were filled,  but some weren’t.
To avoid updating these records manually these are the following set of db2  queries I ran utilizing a Sequence object and a Concat function.

Step 1: I added a test field to the target table

Step 2: Upon altering the table structure you MUST run a REORG command to re-organize indexes, otherwise you’d get some strange error message.

Step 3: I created a sequence and called it HOSTNAMENUM

Step 4: I ran a command to update the newly added field, TESTFIELDX with the incremental values, in addition, I wanted to append the values with strings, like so:


Step 5: Copy the newly filled-out field, to your source field, like so:

Note: the condition WHERE SOURCE_FIELD = '' denotes that I only want to update the fields which are blank

That’s all folks.

Thank you


Leave a Reply