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