CODEWITHSUNDEEP
mysqlsqldatabase
bodacious
bodacious

Reputation: 6705

How can I auto-increment this table without losing data?

I have a table on my live database with several thousand records.

Tracking a bug this morning I found that the table "id" column wasn't set to auto_increment so I have 1000s of records with id = 0.

:(

mysql> explain feed_items;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | int(11)      | NO   |     | 0       |       |
| feed_id      | int(11)      | YES  | MUL | NULL    |       |
| title        | varchar(255) | YES  |     | NULL    |       |
| description  | text         | YES  |     | NULL    |       |
| item_link    | varchar(255) | NO   |     | NULL    |       |
| item_updated | datetime     | YES  |     | NULL    |       |
| item_id      | varchar(255) | YES  |     | NULL    |       |
| created_at   | datetime     | YES  |     | NULL    |       |
| updated_at   | datetime     | YES  |     | NULL    |       |
| is_hidden    | int(11)      | YES  |     | 0       |       |
+--------------+--------------+------+-----+---------+-------+

What's strange is that older records seem to have auto_incremented. So half of my records have the expected id, half of them don't

What I'd like to do is set this column to auto_increment and then run a statement that does the following:

I'm not too hot with SQL though and I don't want to corrupt the data on my live server.

can anyone advise?

Upvotes: 1

Views: 323

Answers (1)

DarkSquirrel42
DarkSquirrel42

Reputation: 10287

depending on the size of your table, and the possibility of taking it offline, you could simply create a 2nd table with the same field schema and insert select your data ...

INSERT INTO _YourSecondTableName_  (feed_id,title,description,item_link,item_updated,item_id,created_at,updated_at,is_hidden)  
SELECT feed_id,title,description,item_link,item_updated,item_id,created_at,updated_at,is_hidden  
FROM _YourFirstTableName_  
ORDER BY created_at ASC

you could then drop the original table and rename the new one ...

Upvotes: 2

Related Questions