Hello i have an sqlite db with many records like 10540 record they are ordered by creation time , i want to shift like a record in the middle and like to do it automatically
for example :
select * from table1 where id >= 8521;
UPDATE Table1 SET id = id +1 ;
does not work i get Error: Result: UNIQUE constraint failed: so i want to shift up all records from 8521 to the last record and get place in the 8520 place for example so i can insert my record in that place of table . even the
id = select max(id)+1
does not work how can i increment the id from last record to the needed record so i can put a place in the records db
A simple update statement would fail, as it would try to create duplicate values in the primary key.
What you can do is this:
First update the column to the negatives of the values they should have:
update table1
set id = -(id + 1)
where id > 8520;
Now there are no duplicates and you just need to update again to the positive values:
update table1
set id = -id
where id < 0;
This will do the trick, but any kind of updating the primary key is not a recommended practice