Update/Increment a single column on multiple rows at once

Question

I'm trying to add rows to a column, keeping the order of the newest column set to one, and all other rows counting up from there.

In this case, I add a new row with order=0, then use this query to update all the rows by one.

"UPDATE favorits SET order = order+1"

However, what happens is that all the rows are updated to the same value. I get a stack of favorites, all with order 6 for example, when it should be one with 1, the next with 2 and so on.

How do I update these rows in a way that orders them the way they should be?

Thanks,
~Jordan

Answer 1

SET @a = 0;  
UPDATE favorits SET order = @a:=@a+1;