SQLite Update Execution Order with UNIQUE

Question

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);

Answer 1

An alternative that doesn't require the table to be changed is to have an intermediate update that sets the new values to be in a range not covered by the range (easy if no values can be negative) that exists and to then update the values to what they should be.

e.g. the following demonstrates this using negative intermediate values :-

-- Load the data
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (id INTEGER PRIMARY KEY, bar INTEGER UNIQUE);
WITH RECURSIVE cte1(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM cte1 LIMIT 100)
    INSERT INTO foo (bar) SELECT * FROM cte1;
-- Show the original data
SELECT * FROM foo;
UPDATE foo SET bar = 0 - (bar + 5) WHERE bar > 17;
UPDATE foo SET bar = 0 - bar WHERE bar < 0;
-- Show the end result
SELECT * FROM foo;

Result 1 - Original Data

Result 2 - Updated data :-