update rownum

przytula_guy · #1 (**permalink**) 12-23-11, 06:05

I have a problem with a specific query and I know there are some sql special-x that spot this forum.
I have a simple table and can get the rownum for rows
sample query below with output

select a.rownum ,b.id,b.seq_number from batch.message b, ( select id,ROW_NUMBER() OVER (ORDER BY (CASE WHEN FORM='T820' THEN 1 ELSE 0 END), ID ASC) ROWNUM FROM BATCH.MESSAGE) a where a.id=b.id

ROWNUM ID SEQ_NUMBER
-------------------- -------------------- --------------------
1 1 -
2 2 -
3 3 -
4 4 -
10 5 -
11 6 -
12 7 -
13 8 -
5 9 -
6 10 -
7 11 -
8 12 -
9 13 -

the problem now is that we want to update the column seq_number with the value of rownumber of previous select by id
or I am getting always 1 as result or I always get and error indicating the select returns more than 1 row
any help is welcome..
thanks for all update/help

tonkuma · #2 (**permalink**) 12-23-11, 07:31

It is better to publish before data of batch.message and CREATE TABLE statement of batch.message.
The values of form column are not known for me and other than you.

Anyway, try this...
Example 1:

Code:

UPDATE
(SELECT id
      , seq_number
      , ROW_NUMBER()
           OVER( ORDER BY CASE
                          WHEN FORM = 'T820' THEN
                               1
                          ELSE 0
                          END
                        , ID ASC ) ROWNUM
  FROM  batch.message
)
SET seq_number = rownum
;

Note: I tested the update statement on DB2 9.7.5 on Windows/XP
using some guess for columns of batch.message and values of form column.
It might not work on some other environment.

tonkuma · #3 (**permalink**) 12-23-11, 07:42

Test data:

Code:

------------------------------ Commands Entered ------------------------------
CREATE TABLE batch.message
( id         INTEGER NOT NULL
, seq_number INTEGER
, form       CHAR(4)
);
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
INSERT INTO batch.message
(id , form)
VALUES
  (  1 , 'T860' )
, (  2 , 'T850' )
, (  3 , 'T840' )
, (  4 , 'T830' )
, (  5 , 'T820' )
, (  6 , 'T820' )
, (  7 , 'T820' )
, (  8 , 'T820' )
, (  9 , 'T810' )
, ( 10 , 'T790' )
, ( 11 , 'U810' )
, ( 12 , 'U850' )
, ( 13 , 'S820' );
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
SELECT *
 FROM  batch.message;
------------------------------------------------------------------------------

ID          SEQ_NUMBER  FORM
----------- ----------- ----
          1           - T860
          2           - T850
          3           - T840
          4           - T830
          5           - T820
          6           - T820
          7           - T820
          8           - T820
          9           - T810
         10           - T790
         11           - U810
         12           - U850
         13           - S820

  13 record(s) selected.

batch.message after updated by Example 1:

Code:

------------------------------ Commands Entered ------------------------------
SELECT *
 FROM  batch.message;
------------------------------------------------------------------------------

ID          SEQ_NUMBER  FORM
----------- ----------- ----
          1           1 T860
          2           2 T850
          3           3 T840
          4           4 T830
          5          10 T820
          6          11 T820
          7          12 T820
          8          13 T820
          9           5 T810
         10           6 T790
         11           7 U810
         12           8 U850
         13           9 S820

  13 record(s) selected.

przytula_guy · #4 (**permalink**) 12-23-11, 08:13

understood --- many many thanks..

tonkuma · #5 (**permalink**) 12-23-11, 14:29

Tried to make the query shorter.

Example 2:

Code:

------------------------------ Commands Entered ------------------------------
UPDATE (
SELECT seq_number
     , ROW_NUMBER() OVER (
          ORDER BY POSSTR(form , 'T820') , id
       ) rownum
 FROM  batch.message
)
SET seq_number = rownum
;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
SELECT *
 FROM  batch.message;
------------------------------------------------------------------------------

ID          SEQ_NUMBER  FORM
----------- ----------- ----
          1           1 T860
          2           2 T850
          3           3 T840
          4           4 T830
          5          10 T820
          6          11 T820
          7          12 T820
          8          13 T820
          9           5 T810
         10           6 T790
         11           7 U810
         12           8 U850
         13           9 S820

  13 record(s) selected.

Internet Services

Web Development

Digital Marketing

Consumer Tech