Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Unanswered: populating column with sequential values

    trying to use SQL to populate a column in a DB2 table with sequential values starting at a number determined after the table is created (via a select process).
    Thought I could use a row number to add to the start number but cannot seem to do it....
    any help would be appreciated.

  2. #2
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    62
    Hi

    Must the numbers be consecutive? If not, you can use a column, by defining it with GENERATED ALWAYS (or BY DEFAULT) AS IDENTITY START WITH n. Then DB2 increments this value each time, when you insert a new row.

    If they have to be consecutive, the only way is - and this could be very, very time consuming - to do a SELECT COUNT(*).

  3. #3
    Join Date
    Mar 2004
    Posts
    4
    Originally posted by Walter Janissen
    Hi

    Must the numbers be consecutive? If not, you can use a column, by defining it with GENERATED ALWAYS (or BY DEFAULT) AS IDENTITY START WITH n. Then DB2 increments this value each time, when you insert a new row.

    If they have to be consecutive, the only way is - and this could be very, very time consuming - to do a SELECT COUNT(*).
    Thx - yes numbers must be consecutive starting at a variable value.
    I was hoping I could use some snappu SQL to update a table that looks like this:

    COLA COLB
    ------ ------
    abc
    def
    ghi
    ...
    etc

    To this:
    COLA COLB
    ------ ------
    abc 86
    def 87
    ghi 88
    ... ....
    etc
    COLA

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Like this...
    Code:
    select colname, row_number() over()
    from tabname

  5. #5
    Join Date
    Mar 2004
    Posts
    4
    Originally posted by Damian Ibbotson
    Like this...
    Code:
    select colname, row_number() over()
    from tabname

    Thanks, but Row_number() over() is not supported by the mainframe DB2 version I am working with (v7.1 I believe) Running this thru QMF I get an SQL error

  6. #6
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Thanks, but Row_number() over() is not supported by the mainframe DB2 version I am working with (v7.1 I believe) Running this thru QMF I get an SQL error
    You can use a unique key within your table to generate an incremental index in the following manner. The join would be a little more complex if the unique key is composite but the concept is the same.
    Code:
    select a.col1, a.col2, ... , count(*)
    from yourTable a
    ,       yourTable b
    where a.uniqueCol <= b.uniqueCol
    group by a.col1, a.col2, ...
    Performance will suck though!

    Damian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •