Re-number MySQL field

Question

I currently have a table in MySQL (below) where I need to re-number the Lane field to start from 1, and increment only on a change of the original Lane value.

As an example, a desired table outcome is also listed below.

There isn't a calculated link between the new Lane number and the old Lane number.

Original Table

Class

ClassID|Lane  
1      |8  
2      |8  
3      |11  
4      |11  
5      |11  
6      |12  
7      |18  
8      |18  
9      |18  
10     |21  
11     |22  
12     |22  
13     |22

Desired Result

Class

ClassID|Lane  
1      |1  
2      |1  
3      |2  
4      |2  
5      |2  
6      |3  
7      |4  
8      |4  
9      |4  
10     |5  
11     |6  
12     |6  
13     |6

The original table is nearly 7000 rows, so do it manually is not viable. (I did start, but figured out it was more difficult than I expected.)

Any help would be greatly appreciated.

peterm · Accepted Answer · 2014-02-16 04:33:14Z

3

You can do

SET @n := 0, @l := NULL;

UPDATE class c JOIN
(
  SELECT classid, lane, @n := IF(@l = lane, @n, @n + 1) new_lane, @l := lane
    FROM class
   ORDER BY classid, lane
) t 
    ON c.classid = t.classid
   SET c.lane = t.new_lane;

Here is SQLFiddle demo

or (initialing variables right in the derived table)

UPDATE class c JOIN
(
  SELECT classid, lane, @n := IF(@l = lane, @n, @n + 1) new_lane, @l := lane
    FROM class CROSS JOIN (SELECT @n := 0, @l := NULL) i
   ORDER BY classid, lane
) t 
    ON c.classid = t.classid
   SET c.lane = t.new_lane;

Here is SQLFiddle demo

Outcome:

| CLASSID | LANE |
|---------|------|
|       1 |    1 |
|       2 |    1 |
|       3 |    2 |
|       4 |    2 |
|       5 |    2 |
|       6 |    3 |
|       7 |    4 |
|       8 |    4 |
|       9 |    4 |
|      10 |    5 |
|      11 |    6 |
|      12 |    6 |
|      13 |    6 |

edited Feb 16, 2014 at 4:33

answered Feb 16, 2014 at 3:15

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gordon Linoff Over a year ago

. . The only change I would make is to use a subquery to initialize the variable. I usually do this with something like: from class cross join (select @n := 0, @l := NULL) const.

peterm Over a year ago

@GordonLinoff Thanks for your edit (missing colon) and the input. Agreed. I always do it the same way when it's a SELECT on its own.

Gordon Linoff · Accepted Answer · 2014-02-16 03:15:56Z

1

Here is a query that returns the result that you want:

select classId,
       (select count(distinct Lane)
        from table t2
        where t2.ClassId <= t.ClassId
       ) as Lane
from table t;

If you actually need to update the value, you can do this using an update with a join:

update table toupdate join
       (select classId,
               (select count(distinct Lane)
                from table t2
                where t2.ClassId <= t.ClassId
               ) as Lane
        from table t
       ) newval
       on toupdate.classid = newval.classid
    set toupdate.lane = newval.lane;

answered Feb 16, 2014 at 3:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

elixenide Over a year ago

This doesn't answer the question; OP has only one table, but wants to change the values in that table.

Gordon Linoff Over a year ago

@EdCottrell . . . It is not always clear when an OP wants a query result or an update. But I have edited the answer to include the appropriate update statement.

elixenide Over a year ago

Good edit; your answer now adequately addresses both possibilities.

Collectives™ on Stack Overflow

Re-number MySQL field

Original Table

Desired Result

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Original Table

Desired Result

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related