Nice, but that still requires extra work. 1) Determine if row is already in table. 2) Determine next lower value. 3) Split difference and insert.
There's also the possibility that the higher level APP expects the new row to have a sequence number of 3. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of kyan Sent: Friday, September 06, 2013 10:41 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] UPDATE question 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 This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
