Re: Increasing a column values partially

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 - 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?
>
> It's a - INSERT INTO table VALUE col, (col+1) WHERE col >=2 , essentially,
> in pseudocode. How? :)

I would treat this as a two-step transaction:

1. update all rows following the (to be) inserted row, and
2. insert the new row

so, the SQL would look something like
  START TRANSACTION;
[Quoted]   UPDATE table SET col = col + 1 where col >= 2 ORDER BY col;   INSERT INTO table (col) VALUES (2);
  COMMIT; Caveat: I have not seen your table definition, nor your table contents. While this approach should work, the details are in your hands.

HTH

-- 
Lew Pitcher
"In Skills, We Trust"
PGP public key available upon request