Updating multible rows in sqllite causes unique constraint exception

Question

I have a table with a column that I want to have a unique constraint on. Then I want to execute a query that updates the value of that column and the end result should not violate the unique constraint. This works perfectly in mssql but sqllite gloriously fails this task, throwing an unique constraint exception

Here is a example

Create table page (
    id BLOB NOT NULL CONSTRAINT "PK_Page" PRIMARY KEY,
    Name nvarchar(200),
    ParentId BLOB NOT NULL,
    Placement int
);

CREATE UNIQUE INDEX "IX_Page_ParentId_Placement" ON "Page" ("ParentId", "Placement");

insert into page (id,name,parentid,placement) values ('id1','A','r',1);
insert into page (id,name,parentid,placement) values ('id2','A','r',2);
insert into page (id,name,parentid,placement) values ('id3','A','r',3);
insert into page (id,name,parentid,placement) values ('id4','A','r',4);


update page set placement = placement + 1;

https://www.db-fiddle.com/f/rSz1iU1jkcDBdahqJNqGat/0

Reversing the order of the insert statements works, but that would not work in production.

Is there solution to this?

Answer 1

One method is to use two updates:

update page
    set placement = - (placement + 1);

update page
    set placement = - (placement + 1);