I need to multiply a certain row in a DB2 table 18000 times. Some of the column values will remain as the original and some need to be incremented by 1.
I have very little knowledge in DB2 and I just can't find a concrete simple answer on how to do this. Can someone please give me an example on how to achieve this?
DB2 version: 9.7 / OS: Windows 2k8
For example I have the following table:
T_RES_TABLE
Col1 |Col2|Col3 |Col4|
----------------------
1 | 1| 1| 1|
What I need to achieve is:
T_RES_TABLE
Col1 |Col2|Col3 |Col4|
----------------------
1 | 1| 1| 1| - original
----------------------
2 | 1| 2| 1|
----------------------
.
.
.
----------------------
18000| 1|18000| 1|
So Col1 and Col3 need to increment and the rest must stay as is. Hope it's clear enough.
You can use a recursive query to generate new column values:
$ db2 "create table t_res_table (col1 int, col2 int, col3 int, col4 int)"
DB20000I The SQL command completed successfully.
$ db2 "insert into t_res_table values (1,1,1,1)"
DB20000I The SQL command completed successfully.
$ db2 "select * from t_res_table"
COL1 COL2 COL3 COL4
----------- ----------- ----------- -----------
1 1 1 1
1 record(s) selected.
$ db2 "insert into t_res_table \
>with t (col1, col2, col3, col4, lvl) as ( \
>select col1, col2, col3, col4, 1 from t_res_table where col1 =1 \
>union all \
>select col1+1, col2, col3+1, col4, lvl+1 from t where lvl <18) \
>select col1, col2, col3, col4 from t where col1 > 1"
DB20000I The SQL command completed successfully.
$ db2 "select * from t_res_table"
COL1 COL2 COL3 COL4
----------- ----------- ----------- -----------
1 1 1 1
2 1 2 1
3 1 3 1
4 1 4 1
5 1 5 1
6 1 6 1
7 1 7 1
8 1 8 1
9 1 9 1
10 1 10 1
11 1 11 1
12 1 12 1
13 1 13 1
14 1 14 1
15 1 15 1
16 1 16 1
17 1 17 1
18 1 18 1
18 record(s) selected.
This will do your job. Just change it as your needs, change table name and column names and hit run.
I dont know what you want to achieve but, it sounds you just need an Dummy Data in a table. So, i just make it for dummy purpose.
Modify it for your another requirements. The example below contains everything what you need, but you may have to modify according to that.
DECLARE @I INT=0
SET @I=1
DECLARE @LASTF2 INT
DECLARE @LASTF4 INT
SELECT @LASTF2=ISNULL(MAX(F2),1) FROM TEST --JUST CHANGE TEST TO YOUR TABLENAME, AND F1,F2,F3,F4
SELECT @LASTF4=ISNULL(MAX(F4),1) FROM TEST -- AS YOUR FIELD NAMES
WHILE @I<10000
BEGIN
INSERT INTO TEST (F1,F2,F3,F4)
VALUES (@I,@LASTF2,@I,@LASTF4)
SET @I=@I+1
END
GO
SELECT * FROM TEST
