Re: Increasing a column values partially
From: The Natural Philosopher <tnp_at_invalid.invalid>
Date: Mon, 29 May 2017 15:49:09 +0100
Message-ID: <oghc6l$k81$1_at_dont-email.me>
>
> How does that relate to a primary key? It doesn't at all. It's more like a
> sorting criteria
>
>
> Preferably you don't do that at all. If you need an ordering column with the
> ability to insert between adjacent rows, use either a FLOAT or DOUBLE
> column. Or insert rows with bigger increments - lets say 1000 - of the
> ordering column. Then you can insert quite many rows between neighbors
> before you need to renumber.
>
> Ever heard of the BASIC programming language? It uses line numbers with a
> default spacing of 10, so that you can insert lines between existing lines
> easily. It also has a RENUMBER command to expand line number to multiples of
> 10 again (handling line number references).
>
>
> You *could* use an UPDATE ... ORDER BY statement for that. But I *strongly*
> advise not to. Never touch existing rows unless the actual *data* of those
> rows has changed.
>
Date: Mon, 29 May 2017 15:49:09 +0100
Message-ID: <oghc6l$k81$1_at_dont-email.me>
On 29/05/17 15:06, Axel Schwenke wrote:
> On 29.05.2017 15:35, bit-naughty_at_hotmail.com wrote:
>> I have a column, say col, which has nos. sequentially, like 1,2,3, etc. ..... >> sorta like a primary key, but only not
>
> How does that relate to a primary key? It doesn't at all. It's more like a
> sorting criteria
>
>> if I want to insert a new row which will be IN BETWEEN, eg. a new line with >> the no. "2" between 2 and 3, all rows with the column value 2 onwards need >> that value inc'd by 1 (I think this is what they call a linked list). >> How do I do this in MySQL?
>
> Preferably you don't do that at all. If you need an ordering column with the
> ability to insert between adjacent rows, use either a FLOAT or DOUBLE
> column. Or insert rows with bigger increments - lets say 1000 - of the
> ordering column. Then you can insert quite many rows between neighbors
> before you need to renumber.
>
> Ever heard of the BASIC programming language? It uses line numbers with a
> default spacing of 10, so that you can insert lines between existing lines
> easily. It also has a RENUMBER command to expand line number to multiples of
> 10 again (handling line number references).
>
>> It's a - INSERT INTO table VALUE col, (col+1) WHERE col >=2 , essentially, >> in pseudocode. How? :)
>
> You *could* use an UPDATE ... ORDER BY statement for that. But I *strongly*
> advise not to. Never touch existing rows unless the actual *data* of those
> rows has changed.
>
In the real world, the best way to do this is to have a separate table with 'column numbers' in it and a field pointing to the unique ID of the primary table
You can delete rows, you can add rows but you should NEVER EVER renumber the primary row Keys.
So break the relationship between primary ID and what it is that you need to have, like a sequential search key or similar, by creating another table that references the primary table
-- Ideas are more powerful than guns. We would not let our enemies have guns, why should we let them have ideas? Josef StalinReceived on Mon May 29 2017 - 16:49:09 CEST