Re: remove gaps in a table

[Quoted] 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

GM Received on Wed Dec 20 2017 - 02:02:36 CET