#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0

    Sequencing integer value for rows based on table ID


    Dear All,

    I have following problem with altering the table.

    Original table with two columns

    seq seq_id
    1 0
    1 0
    1 0
    1 0
    2 0
    2 0
    2 0
    3 0
    3 0
    3 0
    4 0
    4 0
    4 0
    4 0
    4 0

    Now I want to sequence the second column according to the ID of the first column. The resulted table shall look like
    seq seq_id
    1 1
    1 2
    1 3
    1 4
    2 1
    2 2
    2 3
    3 4
    3 5
    3 6
    4 1
    4 2
    4 3
    4 4
    4 5

    Would be very appreciate if someone has solved this issue before. Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Code:
    CREAATE TABLE new
    ( seq INTEGER NOT NULL
    , seq_id INTEGER NOT NULL AUTO_INCREMENT
    , PRIMARY KEY ( seq,seq_id )
    );
    INSERT INTO new_table ( seq )
    SELECT seq from old_table
    ;
    DROP TABLE old_table
    ;
    RENAME new_table TO old_table
    ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0
    Thank you very much for your support. Excellent answer
    I made it done now.
    I have to add ENGINE=MyISAM to make it work.


    CREATE TABLE new
    ( seq INTEGER NOT NULL
    , seq_id INTEGER NOT NULL AUTO_INCREMENT
    , PRIMARY KEY ( seq,seq_id )
    ) ENGINE=MyISAM;
    INSERT INTO new_table ( seq )
    SELECT seq from old_table
    ;
    DROP TABLE old_table
    ;
    RENAME new_table TO old_table
    ;


    best,

    Nam,
    ETHZ, Zurich

IMN logo majestic logo threadwatch logo seochat tools logo