No one commented on my second thread (written after I actually understood the problem!).
But, I proposed a two update sequence to do it. UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >= seq_to_insert AND Name = name_to_insert UPDATE table SET Sequence = -Sequence WHERE Sequence < 0 AND Name = name_to_insert I've used this system many times to avoid conflicts, but it may not work where the table needs to be accessed concurrently, as rows will sort of disappear temporarily (or at least change to an unusable state). -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dominique Devienne Sent: Friday, September 06, 2013 3:28 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] UPDATE question On Fri, Sep 6, 2013 at 6:21 AM, James K. Lowden <jklow...@schemamania.org>wrote: > Here's how Marc L. Allen's query should work (different DBMS): > > $ bsqldb < /tmp/sql > PKey Name Sequence > ----------- ------------------------------ ----------- > 1 Blue 1 > 2 Blue 2 > 3 Blue 4 > 4 Blue 5 > 5 Blue 6 > 6 Blue 3 > 6 rows affected > > Note that the final insert is assigned the next auto-generated PKey > (6), and the old 3 is now 4, etc. > FWIW, a repro, taking the unrelated PKey out of the picture: C:\Users\DDevienne>sqlite3 SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table jkl (name text, seq number, unique (name, seq)); sqlite> insert into jkl values ('blue', 1), ('blue', 2), ('blue', 3), ('blue', 4), ('blue', 5); sqlite> select * from jkl; blue|1 blue|2 blue|3 blue|4 blue|5 sqlite> update jkl set seq = seq + 1 where seq >= 3 and name = 'blue'; Error: columns name, seq are not unique sqlite> I tried to "ruse" and use sqlite> update jkl set seq = seq + 1 where name = 'blue' and seq in sqlite> (select seq from jkl where seq >= 3 order by seq desc); Error: columns name, seq are not unique but of course you cannot influence the processing order SQLite uses. OK, you can in a way, see below: sqlite> create table jk2 as select * from jkl order by seq desc; select sqlite> * from jk2; blue|5 blue|4 blue|3 blue|2 blue|1 sqlite> update jk2 set seq = seq + 1 where seq >= 3 and name = 'blue'; sqlite> select * from jk2; blue|6 blue|5 blue|4 blue|2 blue|1 sqlite> insert into jk2 values ('blue', 3); select * from jk2; blue|6 blue|5 blue|4 blue|2 blue|1 blue|3 sqlite> By forcing the "physical order" of the rows to be reversed, the UPDATE succeeds. I suspect this is just a bug, and Dr Hipp will fix it (and if not document it somehow). For reference, the same in Oracle: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create table jkl 2 ( name varchar2(64) 3 , seq number 4 , unique (name, seq) 5 ); Table created. SQL> insert into jkl values ('blue', 1); 1 row created. SQL> insert into jkl values ('blue', 2); 1 row created. SQL> insert into jkl values ('blue', 3); 1 row created. SQL> insert into jkl values ('blue', 4); 1 row created. SQL> insert into jkl values ('blue', 5); 1 row created. SQL> commit; Commit complete. SQL> column name format a16; SQL> select * from jkl; NAME SEQ ---------------- ---------- blue 1 blue 2 blue 3 blue 4 blue 5 SQL> update jkl set seq = seq + 1 where seq >= 3 and name = 'blue'; 3 rows updated. SQL> select * from jkl; NAME SEQ ---------------- ---------- blue 1 blue 2 blue 4 blue 5 blue 6 SQL> insert into jkl values ('blue', 3); 1 row created. SQL> select * from jkl; NAME SEQ ---------------- ---------- blue 1 blue 2 blue 4 blue 5 blue 6 blue 3 6 rows selected. SQL> _______________________________________________ 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
