0

I have a column 'id' and right now the values are 1, 2, 3, 4 etc. This is my primary column which is set to auto increment.

But I want to update this column and change all the values, starting at a certain number. Not resetting the auto increment, but changing what's already there.

So for example, I want it to start at 1000, then each row increments up by 1, to 1001, 1002, etc. How can this be done?

Thanks.

  • You could run an `UPDATE` command adding some fixed number to the current `id` value and then set the `AUTO_INCREMENT` value to be the new `MAX(id)+1` value. If the table is in active use, it would probably actually be better to calculate the new max value first, set the auto_inc value, and then run the update. – Patrick Q Apr 30 '14 at 15:07
  • 1
    This is no duplicate since it's not about inserting new data with an adjusted auto_increment, but to readjust an auto_increment value for existing data. – fancyPants Apr 30 '14 at 15:22
  • Just found this example, it works beautifully, just what I wanted: http://javier.rodriguez.org.mx/index.php/2008/08/31/assign-a-consecutive-number-to-each-row-in-a-mysql-table –  Apr 30 '14 at 16:31

1 Answers1

2

A self explaining test:

> create table ainc(id int auto_increment primary key);
Query OK, 0 rows affected (0.14 sec)

> insert into ainc values (null), (null), (null), (null);
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

> select * from ainc;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.01 sec)

> update ainc set id = id + 1 where id > 2;
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

> update ainc set id = id + 1 where id > 2 order by id desc;
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2  Changed: 2  Warnings: 0

> select * from ainc;
+----+
| id |
+----+
|  1 |
|  2 |
|  4 |
|  5 |
+----+

Summary: Just make use of the supported order by in the update statement.

Then don't forget to adjust your auto_increment value with

ALTER TABLE ainc AUTO_INCREMENT = 5;

or you will get this:

> insert into ainc values (null); 
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

> ALTER TABLE ainc AUTO_INCREMENT = 5;
Query OK, 4 rows affected (0.52 sec)
Records: 4  Duplicates: 0  Warnings: 0

> insert into ainc values (null);
Query OK, 1 row affected (0.01 sec)

> select * from ainc;
+----+
| id |
+----+
|  1 |
|  2 |
|  4 |
|  5 |
|  6 |
+----+
Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • That's a great example, however that just increments the current value up by 1. When I try something like id = 1000 + 1, of course I get a duplicate value still. So how would I get it to increment starting at 1000? –  Apr 30 '14 at 16:07
  • Sorry, have to ask, have you not read it properly? The `order by auto_inc_column DESC` is important. This makes sure that first the higher value gets updated, i.e. first the row with 1001 gets updated to 1002 so that you can update the row with 1000 to 1001. If you don't specify the `order by` it might try to update 1000 to 1001 first, but this leads to a duplicate key error. – fancyPants Apr 30 '14 at 17:33
  • Only suggestion I would make is, to increase the auto_increment counter, insert a new row then immediately delete it. Then do your renumbering. `ALTER TABLE ainc AUTO_INCREMENT = 5` is still a DDL command and can be expensive (for InnoDB). For MyISAM, just set it to 0. – Marcus Adams Apr 30 '14 at 18:07