Re: remove gaps in a table

[Quoted] On Wed, 20 Dec 2017 01:01:40 +0100, mireero <mireero_at_free.fr> wrote in comp.databases.mysql:

>On 12/20/2017 12:29 AM, J?rgen Exner wrote:
>> On Tue, 19 Dec 2017 22:03:16 +0100, mireero <mireero_at_free.fr> wrote in
>> comp.databases.mysql:
>>
>> [...]
>>> I'm looking for a way to "reduce" the order_number, to remove gaps, that
>>> is to obtain:
>> [...]
>>> 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.
>>
>> Are you sure this is a good idea? I just imagine a customer calling:
>>
>> "Good morning. This is ACME Corporation customer support. How may I help
>> you".
>> "Good morning. We have an issue with our last order. The order number is
>> 12345".
>> "Just a second, let me look this up. Ah, yes, I see. What is the problem
>> with the 50 anal beads?"
>> "Anal beads? This is the Jesuit Convent of xyz and I am calling about
>> the order for 30 rosaries!".
>
>Thanks for all the answers.
>
>I do get the idea, don't store the order_number in the database.

Au contraire. Typically the order number is _THE_ unique identifier around which everything else revolves. So is must be stored in the DB.

What I do question is your requirement for sequential order numbers. Typically they are not sequential for a variety of reasons, depending on details e.g. cancelled orders, aborted online orders, etc, etc. If sequential order numbers are indeed a must-have for you (I cannot imagine why!), then you need to investigate where those gaps are coming from and fix the process for creating them or disable whatever process is deleting those orders where the gaps are.. The one thing you cannot do is to change an order number after it has been created. At least that is the typical design of a sales DB.

jue Received on Wed Dec 20 2017 - 01:18:38 CET