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??

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??

sorry, mysql does not support ROW_NUMBER()

why don't you do this with an application language like php?