CODEWITHSUNDEEP
mysqlsqlphpmyadmin
gatto
gatto

Reputation: 343

Adding unique IDs' to existing records and column type modification

I'm using PhpMyAdmin where I have a table in my MySQL db where every record has mostly the same ID and the column is a varchar without auto increment. As I heard from several people it is a very bad idea and I would like to fix it.

So I have a table, call it old_table, and I would like change it's id column to int type with auto increment and assign a unique value to every record.

Is it possible? Or what would be the easiest way to achieve it in PhpMyAdmin? Unfortunately I don't have too much experience in this topic so I would really appreciate if somebody could show me the right way.

I have two ideas, but I'm not sure which would be the best and how could I do it in practice.

  1. Just edit the existing table somehow, and add a unique id value to every existing row (inside PhpMyAdmin).

  2. Creating a new table in the same database and load the content from the old table to the new with unique id-s (inside PhpMyAdmin).

Upvotes: 0

Views: 103

Answers (1)

Dan Leksell
Dan Leksell

Reputation: 540

Not sure if you can do this from inside PhpMyAdmin as I don't know if you can execute SQL commands there. But you can definitely do it from some MySQL interface like MySQL Workbench http://dev.mysql.com/downloads/workbench/5.2.html.

What you want to do is create the new table with the id column set as an auto increment integer. Then set the rest of the columns in the new table to be the same names and datatypes in your old table. Finally you want to insert all the data, except the id, from the old table into the new table like so...

insert into new_table (column1, column2, column3)
select column1, column2, column3 from old_table

This will select the data in column1, column2 and column3 into their respective columns in the new table and the id will automatically be set in the new table.

Upvotes: 2

Related Questions