Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2008
    Posts
    9

    Unanswered: Problem in add column and data??

    CREATE TABLE `games` (
    `id` int( 11 ) NOT NULL AUTO_INCREMENT ,
    `valid` tinyint( 1 ) default '1',
    `date_added` datetime default NULL ,
    PRIMARY KEY ( `id` ) ,
    KEY `id` ( `id` )
    ) ENGINE = MYISAM DEFAULT CHARSET = utf8 AUTO_INCREMENT =4229

    This is my table structure I want to add one column change_id with auto increment value but id has primary key and i can't change it becoz some other tables are depends on it and relates to each other by id

    It's huge data so i can't change manually
    ....any other idea how to get table like this

    original table
    id valid date_added
    123 1 2008-12-09
    211 1 2008-12-08
    33 1 2008-11-30
    45 1 2008-10-31
    57 1 2008-10-31
    666 1 2008-08-25

    after change
    id valid date_added change_id
    123 1 2008-12-09 1
    211 1 2008-12-08 2
    33 1 2008-11-30 3
    45 1 2008-10-31 4
    57 1 2008-10-31 5
    666 1 2008-08-25 6

    How to solve it??

  2. #2
    Join Date
    Dec 2008
    Posts
    9
    In this link i got the idea but not able to implement query in mysql

    http://forums.oracle.com/forums/mess...167957#3167957

    Here is the reply...for my question...
    but column name is different..

    Hi,

    If you only want to update existing data, you can do somethig like this:

    SQL> create table t (id number primary key, user_id number, rating number, date_created date, change_id number)
    Table created.
    SQL> insert into t(id, user_id, rating, date_created) values (123, 1, 4, to_date('2008-12-09', 'yyyy-mm-dd'))
    1 row created.
    SQL> insert into t(id, user_id, rating, date_created) values (211, 1, 3, to_date('2008-12-08', 'yyyy-mm-dd'))
    1 row created.
    SQL> insert into t(id, user_id, rating, date_created) values ( 33, 1, 4, to_date('2008-11-30', 'yyyy-mm-dd'))
    1 row created.
    SQL> insert into t(id, user_id, rating, date_created) values ( 45, 1, 3, to_date('2008-10-31', 'yyyy-mm-dd'))
    1 row created.
    SQL> insert into t(id, user_id, rating, date_created) values ( 57, 1, 2, to_date('2008-10-31', 'yyyy-mm-dd'))
    1 row created.
    SQL> insert into t(id, user_id, rating, date_created) values (666, 1, 4, to_date('2008-08-25', 'yyyy-mm-dd'))
    1 row created.
    SQL> commit
    Commit complete.
    SQL> select * from t

    ID USER_ID RATING DATE_CRE CHANGE_ID
    ---------- ---------- ---------- -------- ----------
    123 1 4 08-12-09
    211 1 3 08-12-08
    33 1 4 08-11-30
    45 1 3 08-10-31
    57 1 2 08-10-31
    666 1 4 08-08-25

    6 rows selected.

    SQL> merge into t
    using (select t.id
    ,row_number() over (order by date_created desc) change_id
    from t) s
    on (t.id = s.id)
    when matched
    then
    update set t.change_id = s.change_id
    where t.change_id is null
    Merge successfully completed.
    SQL> select * from t

    ID USER_ID RATING DATE_CRE CHANGE_ID
    ---------- ---------- ---------- -------- ----------
    123 1 4 08-12-09 1
    211 1 3 08-12-08 2
    33 1 4 08-11-30 3
    45 1 3 08-10-31 4
    57 1 2 08-10-31 5
    666 1 4 08-08-25 6

    6 rows selected.

    SQL> drop table t purge
    Table dropped.

    please tell me modify query in mysql so i got the correct output??

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, mysql does not support ROW_NUMBER()

    why don't you do this with an application language like php?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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