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 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.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
