MySQL – Renumber field values

[adsense id=”0514458240″ width=”468″ height=”60″]

This post is to provide some basic techniques for renumbering values in a table. Before continuing it should be noted that if you do not FULLY understand your database data model and the implications of renumbering values this should not be attempted as there may be dependencies between the field being renumbered and other tables.

Consider the following table:

mysql> select * from renumber;
+------+------------+
| id   | name       |
+------+------------+
|    1 | Tom        | 
|    2 | Dick       | 
|    3 | Harry      | 
|   10 | Donald     | 
|   15 | Scooby Doo | 
|   21 | Gerry      | 
+------+------------+
6 rows in set (0.00 sec)

We need to renumber these sequentially starting with a value of 1000. In other words the record Tom will have an id of 1000, Dick will have 1001 and so on. We are to retain the current order. In order for this to work we will user defined session variables.

During the UPDATE we are auto incrementing the user defined session variable.

mysql> set @x = 999;
Query OK, 0 rows affected (0.00 sec)

mysql> update renumber set id = @x:=@x+1 order by id;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select * from renumber;
+------+------------+
| id   | name       |
+------+------------+
| 1000 | Tom        | 
| 1001 | Dick       | 
| 1002 | Harry      | 
| 1003 | Donald     | 
| 1004 | Scooby Doo | 
| 1005 | Gerry      | 
+------+------------+
6 rows in set (0.00 sec)

This could easily have been performed using a different order:

mysql> select * from renumber;
+------+------------+
| id   | name       |
+------+------------+
|    1 | Tom        | 
|    2 | Dick       | 
|    3 | Harry      | 
|   10 | Donald     | 
|   15 | Scooby Doo | 
|   21 | Gerry      | 
+------+------------+
6 rows in set (0.00 sec)

mysql> set @x = 999;
Query OK, 0 rows affected (0.00 sec)

mysql> update renumber set id = @x:=@x+1 order by name;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select * from renumber;
+------+------------+
| id   | name       |
+------+------------+
| 1005 | Tom        | 
| 1000 | Dick       | 
| 1003 | Harry      | 
| 1001 | Donald     | 
| 1004 | Scooby Doo | 
| 1002 | Gerry      | 
+------+------------+
6 rows in set (0.00 sec)