remove gaps in a table

From: mireero <mireero_at_free.fr>
Date: Tue, 19 Dec 2017 22:03:16 +0100
Message-ID: <5a397e95$0$31613$426a74cc_at_news.free.fr>

Hi,

[Quoted] [Quoted] I have a table "orders" which is basically:

create database tmp;
use tmp
create table orders (

	order_id int auto_increment primary key,
	order_number int,
	order_on date

);

insert into orders (order_number, order_on) values

(1, "2017-10-10"),
(2, "2017-10-11"),
(3, "2017-10-20"),
(5, "2017-10-22"),
(1, "2017-11-03"),
(2, "2017-11-10"),
(4, "2017-11-18"),
(9, "2017-11-20"),
(10, "2017-11-21");

Which gives us:
MariaDB [tmp]> select * from orders;    

       │
+----------+--------------+------------+    

       │
| order_id | order_number | order_on |
   

       │
+----------+--------------+------------+    

       │


|        1 |            1 | 2017-10-10 | 


 
 
       │


|        2 |            2 | 2017-10-11 | 


 
 
       │


|        3 |            3 | 2017-10-20 | 


 
 
       │


|        4 |            5 | 2017-10-22 | 


 
 
       │


|        5 |            1 | 2017-11-03 | 


 
 
       │


|        6 |            2 | 2017-11-10 | 


 
 
       │


|        7 |            4 | 2017-11-18 | 


 
 
       │


|        8 |            9 | 2017-11-20 | 


 
 
       │


|        9 |           10 | 2017-11-21 | 


 
 
       │

+----------+--------------+------------+    

       │
[Quoted] 9 rows in set (0.00 sec)

[Quoted]
• order_id is unique [Quoted]
• order_number is unique for a given month and start back at 1 every month.
• there are gaps in the order_id sequence, for example, in october, we have 4 orders with order_number: 1, 2, 3, 5. In november, we have 6 orders with order_number: 1, 2, 4, 9, 10.

[Quoted] [Quoted] I'm looking for a way to "reduce" the order_number, to remove gaps, that is to obtain:

+----------+--------------+------------+    

       │
| order_id | order_number | order_on |
   

       │
+----------+--------------+------------+    

       │


|        1 |            1 | 2017-10-10 | 


 
 
       │


|        2 |            2 | 2017-10-11 | 


 
 
       │


|        3 |            3 | 2017-10-20 | 


 
 
       │


|        4 |            4 | 2017-10-22 | 


 
 
       │


|        5 |            1 | 2017-11-03 | 


 
 
       │


|        6 |            2 | 2017-11-10 | 


 
 
       │


|        7 |            3 | 2017-11-18 | 


 
 
       │


|        8 |            4 | 2017-11-20 | 


 
 
       │


|        9 |            5 | 2017-11-21 | 


 
 
       │

+----------+--------------+------------+

How would you achieve this ?
[Quoted] Is there a "mysql only" way to achieve the desired result ? [Quoted] I guess we could just iterate on all rows and set the order_number, beginning from 1 but I have the feeling I'm missing something.

Note: in the real table, there are thousands of rows.

Thanks,
Michael Received on Tue Dec 19 2017 - 22:03:16 CET