I am trying to do a bulk update to a table that has a UNIQUE constraint on the column I'm updating. Suppose the table is defined by:
CREATE TABLE foo (id INTEGER PRIMARY KEY, bar INTEGER UNIQUE);
Suppose the database contains a series of rows with contiguous integer values in the bar column ranging from 1 to 100, and that they've been inserted sequentially.
Suppose I want put a five-wide gap in the "bar" sequence starting at 17, for example with a query such as this:
UPDATE foo SET bar = bar + 5 WHERE bar > 17;
SQLite refuses to execute this update, saying "Error: UNIQUE constraint failed: foo.bar" All right, sure, if the query is executed one row at a time and starts at the first row that meets the WHERE clause, indeed the UNIQUE constraint will be violated: two rows will have a bar column with a value of 23 (the row where bar was 18, and the original row where bar is 23). But if I could somehow force SQLite to run the update bottom-up (start at the highest value for row and work backward), the UNIQUE constraint would not be violated.
SQLite has an optional ORDER BY / LIMIT clause for UPDATE, but that doesn't affect the order in which the UPDATEs occur; as stated at the bottom of this page, "the order in which rows are modified is arbitrary."
Is there some simple way to suggest to SQLite to process row updates in a certain order? Or do I have to use a more convoluted route such as a subquery?
UPDATE: This does not work; the same error appears:
UPDATE foo SET bar = bar + 5 WHERE bar IN
(SELECT bar FROM foo WHERE bar > 17 ORDER BY bar DESC);
If moving the unique constraint out of the table definition into its own independent index is feasible, implementing Ben's idea becomes easy:
CREATE TABLE foo(id INTEGER PRIMARY KEY, bar INTEGER);
CREATE UNIQUE INDEX bar_idx ON foo(bar);
-- Do stuff
DROP INDEX bar_idx;
-- Update bar values
CREATE UNIQUE INDEX bar_idx ON foo(bar); -- Restore the unique index
If not, something like
CREATE TEMP TABLE foo_copy AS SELECT * FROM foo;
-- Update foo_copy's bar values
DELETE FROM foo;
INSERT INTO foo SELECT * FROM foo_copy;