But be careful, as you can't change all records from 3 to 4 and then 4 to 5, as the 4 to 5 will contain the records you've just moved from 3 to 4.... Canofworms.jpg..... ;)
Thanks, Chris On 15 Oct 2016 5:46 p.m., "Richard Damon" <rich...@damon-family.org> wrote: > On 10/15/16 12:15 PM, Simon Slavin wrote: > >> On 14 Oct 2016, at 2:29pm, Thom Wharton <twhar...@northpointdefense.com> >> wrote: >> >> I want to be able to programmatically insert a new record anywhere in >>> that table. Let's suppose I want to create a new record between the records >>> whose ID are 2 and 3. This new record would need to take the ID of 3, and >>> all subsequent records would need to have their primary keys updated. >>> >>> Is there a way to do this automagically (like a specialized INSERT >>> command?) in Sqlite? >>> >> It's a silly thing to want to do since it makes a nonsense of what IDs >> are for. I think you need to rethink what you're trying to do by changing >> existing IDs. >> >> However, if you really need to do it ... >> >> UPDATE MyTable SET ID = ID + 1 WHERE ID >= 3; >> INSERT INTO MyTable ... >> >> Simon. >> >> And, if any other table refers to records in that table via that ID > field, THEY need to be changed too, all in an 'atomic' transaction. > > > -- > Richard Damon > > _______________________________________________ > 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
