0

I have a table where it contains a column that has no auto-increment.

When I delete a record some gaps appear and I would like to fill those. Specifically I would like to find the lowest number and start from that one and change every row to has its next starting from 4. (I do not care of the order for the numbers lower or equal to 3). Below is an example of how I would like the column to look after the UPDATE.

+---+       +---+ 
| 1 |       | 1 |
+---+       +---+
| 4 |       | 4 |
+---+       +---+
| 9 |       | 5 |
+---+  ->   +---+
|10 |       | 6 |
+---+       +---+
|11 |       | 7 |
+---+       +---+
|15 |       | 8 |
+---+       +---+

If possible I would like to do that with one query since the rows might be two or three hundred and I wouldnt want to loop through so many.

John James
  • 587
  • 3
  • 8
  • 19
  • 4
    Don't worry about the gaps. – Strawberry Oct 02 '17 at 16:41
  • I need the numbers to be of certain order like that, its necessary for my code. – John James Oct 02 '17 at 16:42
  • 1
    Nobody ever needs this. I doubt that your model is the exception. – Strawberry Oct 02 '17 at 16:46
  • 2
    Ugh, thats how I designed it, its not that I hate seeing gaps.. Regardless I'd appreciate if you can treat the question as a question and not decide whether I need it or not. – John James Oct 02 '17 at 16:51
  • 2
    You can do it this way, but it's probably more efficient to change your design rather than requiring every delete to renumber 50% of your rows on average.. Something to consider. – Blorgbeard Oct 02 '17 at 16:52
  • @Blorgbeard depends how frequently deletes happen, and how much efficiency his app gains by assuming ids are consecutive. (I can't think of how this would be true, but I suppose it's possible.) – Bill Karwin Oct 02 '17 at 16:53
  • @Bill yeah, I only said "probably". Without details, we can't know for sure. – Blorgbeard Oct 02 '17 at 16:54

0 Answers0