-
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 !
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|