This question pops up from time to time.
I will show a correct query script to achieve this below, but I want to
emphasize what others have said: Data in an RDBMS has no intrinsic
order, it's all SETs, and if you artificially bestow order to the data
itself (as opposed to the eventual output) then you are doing something
that's very bad in database design.
To be specific, if the fruit in your DB needs ORDER as a property, best
is to add a column called SortOrder or FruitOrder or the like. In this
column you can then assign the values automatically in steps of 10 or
100, so you end up with a table like:
id | fruit | SortOrder
1 | Apple | 100
2 | Pear | 200
3 | Kiwi | 300 etc...
Then inserting:
INSERT INTO fruit(fruit, SortOrder) VALUES ('Banana',150);
is simply trivial. (The 150 can be computed from splitting the
difference between the precedent and decedent). Non-Integer is best.
Eventually though, you might need to do maintenance and reset the gaps
or such.
Anyway, enough preaching - this query script will fix your Situation in
SQLite very fast:
UPDATE fruit SET id = -id-1 WHERE id >= 2;
UPDATE fruit SET id = -id WHERE id < 0;
INSERT INTO fruit (2,'Banana');
Another way:
UPDATE fruit SET id = -(id * 100);
UPDATE fruit SET id = -id WHERE id < 0;
INSERT INTO fruit (150,'Banana');
The reason why this is bad? Mostly a primary Key serves as a lookup for
other tables linking to a very specific record. Imagine your query that
added fruit to recipes where needed has the fruit's primary keys
shuffled, the next day will see some really weird recipes when Banana
ends up where Pear was intended. Next you'll want to insert
Watermelon... :)
Cheers,
Ryan
On 2017/11/19 10:37 PM, Shane Dev wrote:
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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
