I have a small database (4 rows) in SQLite and I would like to insert a new row at the place 2. My table looks like this:

enter image description here

I did use this command to re-numbering my primary key id:

UPDATE OR IGNORE Films SET id = id   1 WHERE id > 1;

but as the result I am getting this:

enter image description here

It means that there is only row 5 with correct number. Can someone help me ?

CodePudding user response：

The update fails (and is ignored) for every line (except for the one with the biggest id) because there is already a line with the same value.

You cannot control the order of the update.

You could use some trick like :

UPDATE Films SET id = id   1000001 WHERE id > 1;
UPDATE Films SET id = id - 1000000 WHERE id > 1;

but a much better solution would be to add a separate column, for example named Order, so that renumbering would not be a problem :

UPDATE Films SET Order = Order   1 WHERE Order > 1;

CodePudding user response：

Thw BWT answer is very suspicious because there is this reply:

Inserting a row at the specific place in SQLite database

claiming that it is possible.