Geek Answers Handbook

Simulate ORDER BY in SQLite UPDATE to deal with uniqueness constraint

I have a table in SQLite 3 like this:

sqlite> .schema
CREATE TABLE table1 (id INTEGER PRIMARY KEY NOT NULL,
                     title TEXT UNIQUE NOT NULL,
                     priority INTEGER UNIQUE NOT NULL);

Here are some sample data to illustrate:

sqlite> SELECT * FROM table1;
id          title       priority  
----------  ----------  ----------
1           a           1         
2           b           2         
3           c           3         
4           d           4

I want to add 1 to priorityall cells with priority > 1. Here is my first attempt:

sqlite> UPDATE table1 SET priority = priority + 1 WHERE priority > 1;
Error: column priority is not unique

This fails, apparently, because the update is not ordered, which allows UPDATE to try to set one of the cells in the column priorityto the value of the existing cell. So here is my second attempt:

sqlite> UPDATE table1 SET priority = priority + 1 WHERE priority > 1
        ORDER BY priority DESC;
Error: near "ORDER": syntax error

This also fails, apparently because my SQLite 3 installation was not compiled with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT option .

, , SQL- SQLite Android, SQLITE_ENABLE_UPDATE_DELETE_LIMIT, , SQLite 3 SQLITE_ENABLE_UPDATE_DELETE_LIMIT. :

sqlite> BEGIN TRANSACTION;
        UPDATE table1 SET priority = priority + 1 WHERE priority > 1;
        END TRANSACTION;
Error: column priority is not unique

, -, , SQLite .

, , . : ; ?

N.B. - .

+4
1

, - , SQLite , .

(SQLite UPDATE ). , priority , ( ) , UNIQUE:

UPDATE table1 SET priority = - (priority + 1) WHERE priority > 1 ;

UPDATE table1 SET priority = - priority WHERE priority < 0 ;
+7

More articles:


All Articles