1

I have a list mapped using Hibernate with an index column. The table looks like this:

mysql> select * from chart;
+----+--------+------+-----------------------+
| id | report | indx | name                  |
+----+--------+------+-----------------------+
|  2 |      1 |    0 | Volume                |
|  3 |      2 |    0 | South Africa (Volume) |
|  5 |      2 |    2 | People                |
|  6 |      2 |    3 | Platforms             |
|  7 |      2 |    4 | People (Gender)       |
+----+--------+------+-----------------------+

As you can see chart id=4 for report=2 with indx=1 has been deleted.

I need to eliminate the gaps so all the indx values for a given report run in sequence from 0. I could write Java code to sort this out but SQL solution would be much easier to deploy.

Anyone know how to do this? I am using MySQL.

David Tinker
  • 9,383
  • 9
  • 66
  • 98

1 Answers1

2

if this technique works on your version of MYSQL you can try

 SET @row := -1;
 UPDATE chart
 SET indx = @row := @row + 1
 WHERE report = 2
 ORDER BY indx

but I think better to change design.

elevener
  • 1,097
  • 7
  • 20