Update/Increment a single column on multiple rows at once
- by Jordan Feldstein
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