How about... ? UPDATE table SET Sequence = Sequence + 1 WHERE Sequence >= seq_to_insert AND Name = name_to_insert
-----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Peter Haworth Sent: Thursday, September 05, 2013 2:21 PM To: sqlite-users Subject: [sqlite] UPDATE question 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 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
