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
|
1 2 |
ALTER TABLE "SCHEMANAME "."TARGETTBALE" ADD COLUMN "TESTFIELDX" VARCHAR(20) |
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.
|
1 |
CALL sysproc.admin_cmd('REORG TABLE SCHEMANAME.TARGETTABLE'); |
Step 3: I created a sequence and called it HOSTNAMENUM
|
1 2 3 4 5 |
CREATE SEQUENCE HOSTNAMENUM START WITH 3 INCREMENT BY 1 NOMAXVALUE NOCYCLE; |
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:
|
1 2 |
UPDATE SCHEMANAME.TARGETTABLE SET TESTFIELDX = CONCAT(CONCAT('usa',HOSTNAMENUM.NEXTVAL),'--00') |
RESULT:
|
1 2 3 4 5 6 7 8 9 10 |
TESTFIELDEX ----------- usa3--00 usa4--00 usa5--00 usa6--00 usa7--00 usa8--00 usa9--00 usa10--00 |
Step 5: Copy the newly filled-out field, to your source field, like so:
|
1 2 3 |
UPDATE SCHEMA.TARGETTABLE SET SOURCE_FIELD = TESTFIELDX WHERE SOURCE_FIELD = '' |
Note: the condition WHERE SOURCE_FIELD = '' denotes that I only want to update the fields which are blank
That’s all folks.
Thank you
