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. Currently I'm doing this by selecting the Pkey values of the entries with sequence >=3, sorting them by descending sequence, then a loop with an UPDATE to set the sequence to sequence+1 for each PKey value so 5 becomes 6, 4 becomes 5, 3 becomes 4. Then I INSERT a new Blue/3 row. That works fine but wondering if there might be a single UPDATE statement that could do this for me. I can use the WHERE clause to select sequence 3,4, and 5 but the UPDATE has to process the rows in descending sequence order to avoid UNIQUE conflicts. Pete lcSQL Software <http://www.lcsql.com> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
