Re: remove gaps in a table

On Wed, 20 Dec 2017 12:01:29 +0100, mireero <mireero_at_free.fr> wrote in comp.databases.mysql:
>Actually, this order_number is the public identifier of the order.
>It is the number shown in the invoice.
>I don't know exactly why there shouldn't be any gaps but anyway that's
>the specification I got.
>For some accounting reasons, I guess.

Then you must ensure that there will never be any gaps created. Ever. _HOW_ do those gaps come into existance? Are there numbers skipped when creating orders? Are there numbers left unused when someone doesn't complete an online order? Are there orders and their number actually deleted if someone cancels an order?
Find out and fix whatever process is creating those gaps in the sequence. Like a cancelled order cannot(!!!) remove the order from the DB. At most it can mark it as 'cancelled' but the order and order number as such must live on..

>Note: my new spec is to fill the gaps with some today's orders,

That is almost as bad as shifting the existing order numbers. From a practical point of view for each gap in the existing sequence I would simply create a new order with type 'void' or if that doesn't fly then with type 'cancelled'.
And then fix the root cause instead of fumbling with treating the symptom.

>and to
>avoid deleting orders from now on.

If deleting orders created those gaps, then at least that's a step in the right direction.

>I'm not sure of the implications with the dates, though... (maybe none,
>as far as I do it before next year).

Why? Do the orders have to be in chronological sequence and in order number sequence?

jue Received on Wed Dec 20 2017 - 19:53:20 CET