Afternoon all, I am replying to this thread because I am a little confused here. From what I have gotten from this thread, someone wants to be able to insert a record into a table based on an arbitrary record number (i.e. if a table already has records number 1, 2, 3, 4, etc., have the ability to insert a new record number 3 and changing the record numbers (old record number) 3, 4, etc.
My confusing is this - are record numbers arbitrary to start with (since the table can be sorted in any manner) and the only reason for the record number column is to insure that the primary key is unique. Thanks Delvin -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jens Alfke Sent: Saturday, 15 October, 2016 13:42 To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key > On Oct 15, 2016, at 11:12 AM, Keith Medcalf <kmedc...@dessus.com> wrote: > >> Is there a way to do this automagically (like a specialized INSERT >> command?) in Sqlite? > > Unfortunately no, there is no way to do this on *ANY* database that uses the > relational database model. There’s no need to dive into database theory! I’ll play devil’s advocate and say that this could pretty easily be done in SQLite by writing a simple extension function like in inbetween(a, b) that takes two strings and returns a string that sorts in between them, as I described previously. Then you just make the table’s primary key a string and do INSERT INTO mytable (ID, …) VALUES (inbetween($firstid, $secondid), …) where $firstid and $secondid are the the primary keys of the two rows you want to insert between. In real life you’d probably just implement inbetween() as part of your program instead of as a SQLite function; I just wanted to prove that a relational database can in fact do this. —Jens _______________________________________________ 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
