SELECT with auto incrementing value

To have an auto incrementing value, first assign a variable like so:

SELECT @i:=0;

This can now be incremented in the SELECT query like so:

SELECT *, @i:=@i+1 AS i FROM fruit ORDER BY name;

This will return:

+----------+--------+-----------+------+| fruit_id | name   | somevalue | i    |+----------+--------+-----------+------+|        4 | Apple  |         0 |    1 ||        1 | Banana |         0 |    2 ||        3 | Cherry |         0 |    3 ||        2 | Orange |         0 |    4 |+----------+--------+-----------+------+

Notice that for each record, i is one greater than the previous record.

UPDATE with auto incrementing value

The next example updates the "somevalue" column with an incrementing value. Note that needs to be reset otherwise it will continue in this example with 5 being the next number.

SELECT @i:=0;

As with the SELECT query above, we'll update ordering by name:

UPDATE fruit SET somevalue = @i:=@i+1 ORDER BY name;

And the result from "SELECT * FROM fruit"

+----------+--------+-----------+| fruit_id | name   | somevalue |+----------+--------+-----------+|        1 | Banana |         2 ||        2 | Orange |         4 ||        3 | Cherry |         3 ||        4 | Apple  |         1 |+----------+--------+-----------+

or "SELECT * FROM fruit ORDER BY name"

+----------+--------+-----------+| fruit_id | name   | somevalue |+----------+--------+-----------+|        4 | Apple  |         1 ||        1 | Banana |         2 ||        3 | Cherry |         3 ||        2 | Orange |         4 |+----------+--------+-----------+