On Thu, Sep 5, 2013 at 9:20 PM, Peter Haworth <p...@lcsql.com> wrote:

> I have a table with the following (simplified) structure
>
> PKeyINTEGER PRIMARY KEY
> NameTEXT
> Sequence INTEGER
>
> The Name/Sequence pair of columns is defined as UNIQUE
>
> I need to insert a new row into a point between two sequence numbers.  For
> example, if the existing rows are:
>
> Name   Sequence
> -------    ------------
> Blue           1
> Blue           2
> Blue            3
> Blue           4
> Blue           5
>
> ... I might need to insert a new Blue/3.
>
> If the Sequence column needs not be consecutive integers but just
specifies order, consider turning it to a float. Then you can insert a row
between two existing rows with Sequence a and b by using their median (a +
b) / 2 as the new row's Sequence:

Blue           1
Blue           2
--> Blue        (2 + 3) / 2 = 2.5
Blue            3
Blue           4
Blue           5

and then:

Blue           1
Blue           2
--> Blue        2.25
Blue        2.5
 Blue            3
Blue           4
Blue           5

and so on. This way you avoid having to modify following rows on each
insertion.

--
Constantine Yannakopoulos
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to