On 1/21/17, Cecil Westerhof <cldwester...@gmail.com> 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?
BEGIN; ALTER TABLE desktops RENAME TO desktops_old; CREATE TABLE desktops( name TEXT NOT NULL PRIMARY KEY, indexNo INTEGER NOT NULL UNIQUE, value TEXT NOT NULL UNIQUE, waitSeconds INTEGER NOT NULL ); INSERT INTO desktops SELECT name, indexNo+1, value, waitSeconds FROM desktops_old; DROP TABLE desktops_old; COMMIT; The above is just the first method that comes to mind. There are certainly others. For example, you might enforce the uniqueness of indexNo with a separate UNIQUE index, then simply DROP the index before the update and recreate it afterwards. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
