Topic: Update ID field of table

[Omzy]

I have field ID in my table (not auto-incremented), what I wish to do is set the ID field of the first row to 200, and then add 1 to the ID on every next row.

[Dennis1986]

Here's how I would do it:
- Create a temptable with similar fields
- ALTER TABLE [temptable] AUTO_INCREMENT = 200
- INSERT data from original table into temptable
- Empty original table
- INSERT back from temptable to original table

The actual queries you need I'm not going to write right now (I'm sure someone else would want to) as I'm too tired right now.

[Omzy]

How do I insert data from one table to another?

[Dennis1986]

You could also set a variable that increases by 1 each update.

Code: [Select]

SET @temp = 200;
UPDATE table_name SET ID = @temp := ( @temp +1 ) ORDER BY ID ASC;

[Omzy]

How do I insert data from one table to another?

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.0/en/insert-select.html