Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007
    Posts
    26

    Unanswered: update a column with sequence

    Hi
    Please help me regarding


    i have a table with 1 million rows

    in that one primary key column is there

    I want to replace the that column with some sequence

    is it possible

    we can drop that primary key

    need to change that column values to the seduence

  2. #2
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    Suppose your table with 1 mill col is table A
    table A (col1 primary, col2, col3)

    your new table with sequence
    create table B ( col1 primary generated always as identity (start with 1, increment by 1), col2 , col3)

    now you have a couple of options -

    option 1
    now export your data from table A
    export to tableA.ixf of ixf messages msg.txt select col2, col3 from A

    now import / load this data into table B
    import from tableA.ixf of ixf messages msg.txt insert into B (col2, col3)


    option 2
    insert into B select row_number(), col2, col3 from A


    there are lot many other options available... which will be posted over the due course of time
    Last edited by nick.ncs; 01-26-09 at 23:09.
    IBM Certified Database Associate, DB2 9 for LUW

  3. #3
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    I think you don't need to insert the row_number() as the column is already GENERATED IDENTITY. So you need to INSERT INTO B (col2, col3) (SELECT COL2, COL3 from A).

    And while using LOAD/IMPORT, you need to use "modified by identitymissing" option.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

Posting Permissions

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