2017-01-21 18:33 GMT+01:00 R Smith <rsm...@rsweb.co.za>: > > > On 2017/01/21 12:54 PM, Cecil Westerhof wrote: > >> I have the following (work in progress) table: >> CREATE TABLE desktops( >> name TEXT NOT NULL PRIMARY KEY, >> indexNo INTEGER NOT NULL UNIQUE, >> value TEXT NOT NULL UNIQUE, >> waitSeconds INTEGER NOT NULL >> ); >> >> I want to insert a record in front of the others, so indexNo has to be >> increased with one for all records. I would think that this would work: >> UPDATE desktops >> SET indexNo = indexNo + 1 >> >> But it does not, it gives: >> Error: UNIQUE constraint failed: desktops.indexNo >> >> How can I make this work? >> > > My favourite way (only needed in SQLite as this will work in most other > DBs): > > UPDATE desktops SET indexNo = -indexNo - 1 WHERE indexNo >= X; > INSERT INTO desktops ... new row for indexNo X ... ; > UPDATE desktops SET indexNo = -indexNo WHERE indexNo < 0; > > With X being the indexNo at which you wish to Insert the new row. >
I did this. Works. But I need to write some logic, because in the near future I shall also need to reorder the records. > I like this because it's simple, quick, and always works without the need > to calculate anything. If this table is really big (millions of rows) it > /might/ be faster to just drop and recreate the index, you should test the > time difference. Well, at the moment it are nine records and I do not think it will grow much. When in another instance it will, I will look into the performance. -- Cecil Westerhof _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
