MySQL UPDATE values with sequence 1,2,3,

Question

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

MikeTheReader · Accepted Answer · 2010-09-27 20:41:01Z

3

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.

answered Sep 27, 2010 at 20:41

MikeTheReader

4,1903 gold badges27 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

OMG Ponies Over a year ago

That will only return one row, with a value of one.

MikeTheReader Over a year ago

It was meant as a sample, not a definitive solution. The next time you run the select, it will give you back a 2.

cervenak Over a year ago

Thanks. You open my eyes. So this is final SQL. SET @incr = 0; UPDATE myTable SET posit = @incr:=@incr+1 WHERE title='test';

Collectives™ on Stack Overflow

MySQL UPDATE values with sequence 1,2,3,

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related