How can I update the value of a column for two rows that have a unique constraint in SQLite?

Question

I have the following table

CREATE TABLE IF NOT EXISTS node (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  type TEXT CHECK(type IN ("DIRECTORY", "EXECUTABLE", "DOCUMENT")) NOT NULL,
  siblingOrder INTEGER NOT NULL,
  parentId INTEGER,
  UNIQUE (parentId, siblingOrder) ON CONFLICT ABORT,
  FOREIGN KEY(parentId) REFERENCES node(id)
);

No two rows should have the same parentId and siblingOrder.

I am inserting the following data

INSERT INTO node (id, name, type, siblingOrder, parentId) 
VALUES 
(1, "", "DIRECTORY", 0, null),
(2, "Users", "DIRECTORY", 0, 1),
(3, "sys", "DIRECTORY", 1, 1),
(4, "bin", "DIRECTORY", 2, 1),
(5, "Desktop", "DIRECTORY", 0, 2),
(6, "New Folder", "DIRECTORY", 0, 5),
(7, "config", "DOCUMENT", 0, 3),
(8, "README", "DOCUMENT", 1, 5),
(9, "resume", "DOCUMENT", 2, 5),
(10, "Terminal", "EXECUTABLE", 3, 5);

Now, I would like to swap the siblingOrder of, for example, rows with the id of 8 and 9.

If I try this,

UPDATE node 
    SET siblingOrder = CASE id 
        WHEN 8 THEN 2
        WHEN 9 THEN 1
    END
WHERE id IN (8, 9);

it of course fails, because ( I think ) it is attempting to set the siblingOrder of row with id=8 to 2 while row of id=9 still has a siblingOrder of 2 while they share the same parentId.

How can I swap the siblingOrder of these two rows while still maintaining the uniqueness constraint?

Answer 1

From https://stackoverflow.com/a/4798420/15753188

the following has worked for me

UPDATE node 
    SET siblingOrder = CASE id 
        WHEN 8 THEN -2
        WHEN 9 THEN -1
    END
WHERE id IN (8, 9);

UPDATE node
    SET siblingOrder = CASE id
        WHEN 8 THEN 2
        WHEN 9 THEN 1
    END
WHERE id IN (8, 9);