Re: [sqlite] how into insert row into middle of table with integer primary key

SQL in any flavor isn't going to get what you want directly, as you've seen in these posts. The data is in your database for a reason. Its data. It isn't supposed to be manipulated in the way you want to do in this thread. Displaying something in a particular order is done at the application

Re: [sqlite] how into insert row into middle of table with integer primary key

On 22 Nov 2017, at 8:30pm, Shane Dev wrote: > Imagine I have a GUI element with a drop down list of fruit. The source of > the list is my fruit table and it may have many entries. It might more > convenient to list the popular fruit near the top. In that case the >

Re: [sqlite] how into insert row into middle of table with integer primary key

>> Hi, Shane, >> What I don't understand is why do you need to do that? >Imagine I have a GUI element with a drop down list of fruit. The >source of >the list is my fruit table and it may have many entries. It might >more >convenient to list the popular fruit near the top. In that case the

Re: [sqlite] how into insert row into middle of table with integer primary key

Hi, On Wed, Nov 22, 2017 at 2:30 PM, Shane Dev wrote: > On 22 November 2017 at 17:08, Igor Korot wrote: > >> Hi, Shane, >> >> >> What I don't understand is why do you need to do that? >> > > Imagine I have a GUI element with a drop down list of fruit.

Re: [sqlite] how into insert row into middle of table with integer primary key

On 22 November 2017 at 17:08, Igor Korot wrote: > Hi, Shane, > > > What I don't understand is why do you need to do that? > Imagine I have a GUI element with a drop down list of fruit. The source of the list is my fruit table and it may have many entries. It might more

Re: [sqlite] how into insert row into middle of table with integer primary key

> Why do I want store ID numbers > whose values may change? Why not. Because that's not what the row id column is for. Not strictly. That's why it's called 'id' - it's an identification field. You can't (shouldn't) be using it for other means. A database requirement later might need that column

Re: [sqlite] how into insert row into middle of table with integer primary key

Hi, Shane, On Wed, Nov 22, 2017 at 12:40 AM, Shane Dev wrote: > Hi Igor, > > Homework exercise? No, this is purely a hobby project in my free time. My > goal is see how much logic can moved from application code to the database. > > Why do I want store ID numbers whose

Re: [sqlite] how into insert row into middle of table with integer primary key

On 11/22/17, 1:43 AM, "sqlite-users on behalf of R Smith" wrote: > Oh there are many valid reasons why to have Order in data, one I use > regularly is to dictate the process flow in manufacturing where some thing >

Re: [sqlite] how into insert row into middle of table with integer primary key

On 2017/11/22 8:40 AM, Shane Dev wrote: However, this just moves the problem from the id to the sort column. I still have to consider how to manage changes to values in the sort column. Apparently there is no single SQL statement which can insert a record in to any arbitrary sort position. Even

Re: [sqlite] how into insert row into middle of table with integer primary key

Shane. Below is a simple benchmark you can play with to decide if that trigger is fast enough for your application. On the time scale of human thinking and reaction time, I've found SQLite code quite responsive and magnitudes easier to maintain than the equivalent application code. FYI, that

Re: [sqlite] how into insert row into middle of table with integer primary key

Shane Dev wrote: > Why do I want store ID numbers whose values may change? Why not. Because the name "ID" implies that its value _identifies_ the row. If it changes, it is not an ID. > Obviously, this would be bad idea if the ID column was referenced by > other column / table. In that case, I

Re: [sqlite] how into insert row into middle of table with integer primary key

On 2017/11/22 2:29 AM, Jens Alfke wrote: On Nov 21, 2017, at 2:48 PM, Simon Slavin wrote: But that just brings us back to the question of why OP wants to store ID numbers which might change. When I’ve run into this before, the requirement has been to support lists

Re: [sqlite] how into insert row into middle of table with integer primary key

Hi Igor, Homework exercise? No, this is purely a hobby project in my free time. My goal is see how much logic can moved from application code to the database. Why do I want store ID numbers whose values may change? Why not. Obviously, this would be bad idea if the ID column was referenced by

Re: [sqlite] how into insert row into middle of table with integer primary key

On Tue, Nov 21, 2017, 11:10 PM Jens Alfke, wrote: > > It’s a lot better to use strings, and just increase the length of the > string as necessary. So to insert in between “A” and “C” you add “B”, then > to insert between “A” and “B” you add “AM”, etc. > Except that you can't

Re: [sqlite] how into insert row into middle of table with integer primary key

Am 22.11.2017 um 01:29 schrieb Jens Alfke: When I’ve run into this before, the requirement has been to support lists with customizable ordering, like an outliner where the user can freely drag the rows up and down. Yep. And therefore such cases should be handled at the App-Level IMO...

Re: [sqlite] how into insert row into middle of table with integer primary key

> On Nov 21, 2017, at 2:48 PM, Simon Slavin wrote: > > But that just brings us back to the question of why OP wants to store ID > numbers which might change. When I’ve run into this before, the requirement has been to support lists with customizable ordering, like an

Re: [sqlite] how into insert row into middle of table with integer primary key

Simon, On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin wrote: > > > On 21 Nov 2017, at 10:09pm, Jens Alfke wrote: > >>> On Nov 21, 2017, at 1:56 AM, R Smith wrote: >>> >>> That assumes you are not starting from an integer part (like

Re: [sqlite] how into insert row into middle of table with integer primary key

On 21 Nov 2017, at 10:09pm, Jens Alfke wrote: >> On Nov 21, 2017, at 1:56 AM, R Smith wrote: >> >> That assumes you are not starting from an integer part (like 4000) and >> hitting the exact same relative insert spot every time, which /can/ happen,

Re: [sqlite] how into insert row into middle of table with integer primary key

> On Nov 21, 2017, at 1:56 AM, R Smith wrote: > > That assumes you are not starting from an integer part (like 4000) and > hitting the exact same relative insert spot every time, which /can/ happen, > but is hugely unlikely. Not to beat this into the ground, but: it’s

Re: [sqlite] how into insert row into middle of table with integer primary key

On 2017/11/21 7:35 AM, Jens Alfke wrote: On Nov 20, 2017, at 2:05 PM, Simon Slavin wrote: INSERT INTO fruit VALUES ((1 + 2) / 2), 'banana') This gives you a value of 1.5, and puts the new entry in the right place. This solution (which comes up every time this

Re: [sqlite] how into insert row into middle of table with integer primary key

> On Nov 20, 2017, at 2:05 PM, Simon Slavin wrote: > > INSERT INTO fruit VALUES ((1 + 2) / 2), 'banana') > > This gives you a value of 1.5, and puts the new entry in the right place. This solution (which comes up every time this problem is discussed, it seems) is

Re: [sqlite] how into insert row into middle of table with integer primary key

If you want the fruits sorted (and not duplicated), why not just declare that when defining the table? create table fruits (id integer primary key, fruit text collate nocase unique); and if you want to know the "relative row number" of the fruit simply have your application count them:

Re: [sqlite] how into insert row into middle of table with integer primary key

Shane. If you're dead set on paying the cost for brute force mid table id insertion, take a look at INSTEAD OF triggers: https://sqlite.org/lang_createtrigger.html Your example would look like this: CREATE VIEW fruit_ins AS SELECT * FROM fruit; CREATE TRIGGER fruit_ins INSTEAD OF INSERT ON

Re: [sqlite] how into insert row into middle of table with integer primary key

Oops, didn't concentrate, that query should best be: WITH NewOrder(nid,norder) AS (     SELECT F1.id, (SELECT COUNT(*) * 100 FROM fruit AS F2 WHERE F2.SortOrder < F1.SortOrder) FROM fruit AS F1     ORDER BY F1.id -- This last ORDER BY is important as it forces the above correlated

Re: [sqlite] how into insert row into middle of table with integer primary key

On 2017/11/20 11:31 PM, Shane Dev wrote: Hi Ryan, Nice trick - changing the sign of ID. I agree that changing an existing record's ID value would cause problems for any other field / table that referenced this key. If I used your idea of adding a SortOrder column incremented in steps of x,

Re: [sqlite] how into insert row into middle of table with integer primary key

On 20 Nov 2017, at 9:31pm, Shane Dev wrote: > I would to prefer to avoid this solution because it involves mutable state > (the RowCount variable) which is the "root of all evil" (bugs). Is there an > SQL statement which could reset the gaps back to x? As others have

Re: [sqlite] how into insert row into middle of table with integer primary key

Hi Ryan, Nice trick - changing the sign of ID. I agree that changing an existing record's ID value would cause problems for any other field / table that referenced this key. If I used your idea of adding a SortOrder column incremented in steps of x, as you suggested, the gaps would start to

Re: [sqlite] how into insert row into middle of table with integer primary key

On 2017/11/20 6:33 PM, Igor Korot wrote: On Mon, Nov 20, 2017 at 10:12 AM, R Smith wrote: 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

Re: [sqlite] how into insert row into middle of table with integer primary key

Hi, On Mon, Nov 20, 2017 at 10:12 AM, R Smith 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

Re: [sqlite] how into insert row into middle of table with integer primary key

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)

Re: [sqlite] how into insert row into middle of table with integer primary key

On 20 Nov 2017, at 2:57pm, Clemens Ladisch wrote: > Simon Slavin wrote: >> UPDATE fruit SET id = id+1 WHERE id >=2; > > This is unlikely to work because some ID values can conflict in the > middle of the execution. Which in fact violates formal requirements. Im SQL it is

Re: [sqlite] how into insert row into middle of table with integer primary key

If you want to maintain something like a user-selected display order, I would suggest adding an explicit “display order” column. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] how into insert row into middle of table with integer primary key

That actually doesn't work in SQLite as it checks the primary key uniqueness after every row change, not after all updates have been completed. sqlite> update fruit set id = id + 1 where id >= 2; --EQP-- 0,0,0,SEARCH TABLE fruit USING INTEGER PRIMARY KEY (rowid>?) Run Time: real 0.000 user

Re: [sqlite] how into insert row into middle of table with integer primary key

Simon Slavin wrote: > UPDATE fruit SET id = id+1 WHERE id >=2; This is unlikely to work because some ID values can conflict in the middle of the execution. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] how into insert row into middle of table with integer primary key

On 19 Nov 2017, at 8:37pm, Shane Dev wrote: > 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 - > >

Re: [sqlite] how into insert row into middle of table with integer primary key

Shane Dev wrote: > CREATE TABLE fruit(id integer primary key, name text); > > 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? It would be possible, but not easy, especially not in

[sqlite] how into insert row into middle of table with integer primary 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