auto_increment afterthought

smook

I'm wondering if there is a query that I can use to number records without the autoincrement function - I created a table without a primary key ... had data added to it and now realize there are duplicates in it. I want to give each record a unique number so I can accurately remove the duplicates.

Also - any way of adding a 2 column primary key to a table AFTER the table is created?

thanks
sm

Jayson

There's several things you can do. You could make a new table with the primary key with auto increment you wanted, and then copy all the data from the old table to new table going row by row. Or you could add a column to the existing table and run down it putting an incrementing number in each row, then alter it so its a primary key and has auto increment. There may be other ways to do it too.

smook

i've created a new field in the table but i'm not sure how to populate - except by hand. I could write something to populate it with php however as there is no id to begin with its hard to update record by record - i.e.

table0

colour | animal | id

brown cat 0
brown dog 0
brown cat 0
yellow dog 0

can't grab the first record and update it because its the same as the 3rd record.

as far as a new table - I thought this might be my only choice as well but as far as I can see I still have to do this with php - mysql doesn't support "insert..select"

thoughts?

jeremuck

dump the structure and the data into a file. (use the add drop table option)
Change the file so that the structure portion includes an increment key. then use an editor powerful enough to do a search and replace that will apropriately change the beginning of each INSERT so that it includes a blank field where the auto_increment field should be.
so...

when you dump, you'll have:

drop table..
CREATE TABLE mytable (
table stuff....
)

INSERT INTO mytable (fields...) VALUES('firstvalue');

use a text editor and add
an apropriate field with the autoincrement property as the first column.

Then do a replace all for INSERT INTO mytable (fields...
with INSERT INTO mytable(id fields

then do another one for lastfieldname) VALUES('
to lasfieldname) VALUES('', '

Then run the thing.
Should be easy enough if you are using phpmyadmin and a decent editor

when you finish, you'll have something that looks like:

drop table..
CREATE TABLE mytable (
int(11) ID auto_increment primary_keyblahblah
table stuff....
)

INSERT INTO mytable (ID, fields...) VALUES('', 'firstvalue');

hope that helps.

Sxooter

Don't know if this helps, but here's how I do that in postgresql:

alter table yada add id text;
alter table yada alter id set default (nextval('tenk1_id_seq')::text);
update tenk1 set id=nextval('tenk1_id_seq');

I know the functions don't match up, but the theory's the same.