On Fri, Sep 6, 2013 at 6:21 AM, James K. Lowden <[email protected]>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 (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;
sqlite> select * 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);
sqlite> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
