Simulate ORDER BY in SQLite UPDATE to handle uniqu

问题:

I have a table in SQLite 3 thus:

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

Here is some sample data, for illustration:

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

I wish to add 1 to the priority of all 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, presumably because the update is not ordered, allowing the UPDATE to attempt to set one of the cells in the priority column to the value of an 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, too, fails, presumably because my installation of SQLite 3 was not compiled with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT option.

Because I might ultimately want to use my SQL statement(s) with SQLite on Android, and the latter also does not have SQLITE_ENABLE_UPDATE_DELETE_LIMIT enabled, I had better find another way to achieve my aim than re-compiling SQLite 3 with SQLITE_ENABLE_UPDATE_DELETE_LIMIT enabled. Here, then, is my third attempt:

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

This, too, fails, presumably because SQLite checks the uniqueness constraint before committing the transaction.

Three failed attempts, but I'm sure it's possible. The questions are: how to do it; and within that, how best to do it?

N.B. I would prefer not to rely upon any unchecked assumptions.

回答1:

You are right that the problem appears because SQLite checks the constraints after every row update and not at the end of statement or the end of transaction.

I see this workaround to the problem (of SQLite not having implemented UPDATE correctly). Assuming that the priority column does not have any negative values, we can use them (negative values) as temporary to avoid the UNIQUE constraint errors:

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

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