MySQL UPDATE values with sequence 1,2,3, ...

Question

Hello, I have table with position atribute 'posit' with unknown values (in my example '0') and I want to UPDATE it to 1,2,3, ...

BEFORE:

 _______________
| title | posit |
|---------------|
|  test |   0   |
|-------|-------|
|  test |   0   |
|-------|-------|
|  test |   0   |
|-------|-------|
|  test |   0   |
'---------------'

AFTER:

 _______________
| title | posit |
|---------------|
|  test |   1   |
|-------|-------|
|  test |   2   |
|-------|-------|
|  test |   3   |
|-------|-------|
|  test |   4   |
'---------------'

Something like this

UPDATE myTable 
SET posit = last_updated_value() + 1 
WHERE title='test';

Is there any way to do it by SQL command? Note that 'posit' is not auto increment. I have only PHP solution.

Thanks Henry

Answer 1

You have mysql as a tag, so with that you could use a user defined variable. Something like this:

SET @incr = 0;
SELECT @incr:=@incr+1 FROM DUAL;

See http://dev.mysql.com/doc/refman/5.0/en/user-variables.html for more details.