2

I have a table with columns id, name, position, old_position.

The column old_position contains unique numbers that are used to define the order when making select. But the numbers are not consecutive(even if when ordered). E.g. 2, 12, 11, 14, 20, 35, 45, 28,

What I am trying to achieve is to populate the column position with consecutive numbers starting from 1, but with the same order according to old_position, so when I order by position ASC still I can get the same order of rows. Is there a way to do that ?

Thanks

dav
  • 8,931
  • 15
  • 76
  • 140
  • If you are using the values for ordering, why do you care if they are consecutive? – Gordon Linoff Feb 07 '15 at 16:28
  • @GordonLinoff actually the case was a little more complicated and localized, just wanted to keep the question simple. but im sure this is what I need. thanks – dav Feb 07 '15 at 16:33

1 Answers1

2

Assuming that id is unique for each row, you can do this with variables:

update <table> t join
       (select t.id, @rn := @rn + 1 as seqnum
        from <table> t cross join (select @rn := 0) vars
        order by old_position
       ) tt
       on tt.id = t.id
    set t.position = tt.seqnum;

If you wanted, you can write this without the subquery. The challenge is defining the variable. Here is one method:

update <table> t
    set t.position = (@rn := coalesce(@rn, 0) + 1)
    order by old_position;

You can't initialize the variable in a subquery because MySQL doesn't permit both join and order by in an update statement.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • cool !! it worked , very nice. btw, `update table t` should be `update t`, got an error when trying. thank u – dav Feb 07 '15 at 16:36
  • @dav . . . I just use "table" as a generic table name when questions don't provide one. It seems to be a bad habit. – Gordon Linoff Feb 07 '15 at 16:37