Hi,
On Mon, Nov 20, 2017 at 10:12 AM, R Smith <rsm...@rsweb.co.za> wrote:
> 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... :)
For this specific problem you have a FOREIGN KEY... ON UPDATE CASCADE.
But yes - this problem is very weird. Unless its some kind of
educational/home work....
Thank you.
>
> 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
>> 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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
