在SQLite UPDATE中模拟ORDER BY以处理唯一性约束

Question

我在SQLite 3中有一个表，因此：

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

以下是一些示例数据，以供说明：

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

我希望将priority的所有单元格中的加1 priority > 1。这是我的第一次尝试：

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

这可能会失败，大概是因为未按顺序进行更新，从而使UPDATE可以尝试将priority列中的一个单元格设置为现有单元格的值。所以，这是我的第二次尝试：

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

这也失败了，大概是因为我的SQLite
3安装未使用SQLITE_ENABLE_UPDATE_DELETE_LIMIT选项进行编译。

因为我可能最终希望将我的SQL语句与Android上的SQLite一起使用，并且后者也没有启用S​​QLITE_ENABLE_UPDATE_DELETE_LIMIT，所以与重新启用SQLITE_ENABLE_UPDATE_DELETE_LIMIT的SQLite
3相比，我最好找到另一种方法来实现我的目标。那么，这是我的第三次尝试：

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

这也失败了，大概是因为SQLite在提交事务之前检查了唯一性约束。

三次尝试失败，但我确信这是可能的。问题是： 如何做到 ；在那里面， 怎么做最好 呢？

注意：我希望不要依赖任何未经检验的假设。

Answer 1

出现问题是正确的，因为SQLite会在每次更新行之后而不是在语句末尾或事务结束时检查约束。

我看到此问题的解决方法（SQLite的实现不UPDATE正确）。假设该priority列没有任何负值，我们可以将它们（负值）用作临时值，以避免UNIQUE约束错误：

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

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