Re: Increasing a column values partially

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

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

[Quoted] 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? :)

[Quoted] 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. Received on Mon May 29 2017 - 16:06:50 CEST