Add a column to existing table then autopopulate it with ascending numbers...

[frogontop]

I have a client, who after everything is all said and done needs to allow reordering, no problem on the php end, i just need a column named orderid with a unique number. The problem is that there are over 4000 records in the table, so instead of adding the column then manually entering in the numbers 1-4000 I need help on how to do this using phpmyadmin. My ID already uses autoincrement, so I thought there was some clever query i could paste in to take care of this.

 

The table name is ccf_pphotos, so i need to add a column "orderid" then have it autofill with ascending numbers (starting with 1)  . The trick is to do this without affecting anything else in the records.

 

Kinda stumped and don't want to manually edit 4000 records.

 

Any help would be greatly appreciated.

 

Only have phpmyadmin for editing the database (or php).

 

Regards,

 

Jeff

[requinix]

I think you could with something like

SET @orderid = 1; UPDATE table SET orderid = @orderid, @orderid := @orderid + 1

 

Otherwise use a temporary table with its own auto-increment key:

CREATE TABLE temp (orderid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, id INT);
INSERT INTO temp (id) SELECT id FROM table;
UPDATE table JOIN temp ON table.id = temp.id SET table.orderid = temp.orderid;
DROP TABLE temp;

[frogontop]

The second solution you posted worked perfectly, thank you ever so much for you help!