On 05.09.2013 20:20, Peter Haworth 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.
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
Assumed table :
create table (pk integer primary key, name text, seq integer) ;
You can let sqlite handle the creation of a temporary table by :
update t1 set seq=seq+1 where pk in (select pk from t1 where seq >=
3 order by seq desc) ;
afterwards you can insert :
insert into t1 (pk, name, seq) values (6, 'blue', 3) ;
you need the temporary table created by the select pk ... cause of
possible side effects.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
