How to increment one field with one request

Hello,

It sould be easy, but I do not see a way to do it with only on request :

I would to modify one field so the value is equal to the field in the previous row +1. And this on many rows at the same time :

Before :

id | cat | order
---------------------
33 | 2 | 4
34 | 2 | 10
35 | 2 | 9
36 | 2 | 3

after :
I want to modify each fields in the 'order' column where cat=2, so it becomes:

id | cat | order
---------------------
33 | 2 | 1
34 | 2 | 2
35 | 2 | 3
36 | 2 | 4

Could you do that in one query ?

Something like this should do the trick. You need to set a user variable, and then use this to increment the values. Note the field needs to be called orders, not order (a reserved word)

SET @start=0;

UPDATE order_test set orders=(@start:=@start+1) WHERE cat=2 ORDER BY id;

Thank you a lot !

I did not know that variables exist inside a sql request.

Coooooool !