SQLite Forum

table locked when dropping a temp table
Login
The best way to do this in SQLite I've found is to go negative. I've suggested this before (and used it) for INT primary keys.

The script basically goes like this for your use case:

```
UPDATE variables SET order = (0 - (order + 1)) WHERE order >= :order;
INSERT INTO variables ( ... the new row with correct order ...);
UPDATE variables SET order = ABS(order) WHERE order < 0;
```

Easy, Zero duplicate violations, and orders of magnitude faster than temp tables.

PS: Some brackets and spacing added for clarity, it's not all needed.