On Sat, 2017-01-21 at 11:54 +0100, 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? >
I don't think this will work in a single SQL statement. If you start with the largest value of indexNo and work in descending order, it should work. However, this would typically be done in a procedural loop where you can depend on the ordering of a cursor. Maybe somebody knows a clever SQL trick to do it in a single statement? The problem is that you could build a subquery to return the "hole", i.e. the next indexNo to update, but you cannot modify the same table which is used in a subquery of the same UPDATE statement. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
