Possible something like this may work:
create table fruit(id integer primary key, name text);
insert into fruit values
(1,'apple'),
(2,'pear'),
(3,'kiwi');
select * from fruit order by id;
begin;
update fruit set id = -id where id > 1;
update fruit set id = 1-id where id < 0;
end;
insert into fruit values(2,'banana');
select * from fruit order by id;
-----Original Message-----
From: Shane Dev
Sent: Sunday, November 19, 2017 10:37 PM
To: SQLite mailing list
Subject: [sqlite] how into insert row into middle of table with
integerprimary key
Let's say I have a table of fruit -
sqlite> .sch fruit
CREATE TABLE fruit(id integer primary key, name text);
with some entries -
sqlite> select * from fruit;
id|name
1|apple
2|pear
3|kiwi
Is there an easy way to insert 'banana' between apple and pear while still
maintaining a consistent order of the ID field?
desired result -
sqlite> select * from fruit;
1|apple
2|banana
3|pear
4|kiwi
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
