CODEWITHSUNDEEP
mysqlsql
user2441535
user2441535

Reputation:

Modify column values to that they are in order

I got a very special problem that I'd like to solve in SQL. I need to make sure that optionOrder for the same questionID goes from 0-[any number].

So for example the rows with questionID = 18386, their optionOrder are right now 1,2,3,4. They need to be 0,1,2,3.

Also if the rows are like this 1,2,4, it needs to be 0,1,2

I'm sorry for the incorrect grammars.

enter image description here

Upvotes: 0

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270311

In MySQL, you can do this with variables:

set @rn := 0;
set @q := -1;

update table t
    set optionorder = (case when @q = questionid then (@rn := @rn + 1)
                            when (@q := questionid) is not null then (@rn := 0)
                            else -1 -- should never happen
                       end)
    order by questionid, optionorder;

Because of the order by, you need to set the variables outside the update.

Upvotes: 2

Related Questions