Re: remove gaps in a table

From: (wrong string) ürgen Exner <jurgenex_at_hotmail.com>
Date: Tue, 19 Dec 2017 17:13:00 -0800
Message-ID: <g4ej3dh1nttfb6mvsoot21u56s5kfeon1n_at_4ax.com>


[Quoted] On Wed, 20 Dec 2017 12:02:36 +1100, Geoff Muldoon <geoff.muldoon_at_trap.gmail.com> wrote in comp.databases.mysql:

>In article <3gaj3dd0dm04len4iejom72dg8otbrkdo5_at_4ax.com>,
>jurgenex_at_hotmail.com says...
>>
>> 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.
>
>My reading of the original post is that there were TWO fields:
>order_id - autoincrement primary key
>order_number - per month no-gaps business counter, not globally unique

[Quoted] Well, ok, if that's only a "business counter" we are talking about then there is no reason to store it in the DB and a lot of good reasons to not store it in the DB.

jue Received on Wed Dec 20 2017 - 02:13:00 CET

Original text of this message