SQLite (Android) : UPDATE query with ORDER BY

Question

Android, SQLite : I want to insert rows in between other rows in myTable using SQLite in android. For this, I am trying to increment ids of the all rows starting say row 3. So that I can insert a new row at position 3.

The primary key of myTable is column id. There are no other constraints in the table.

I have tried using the query mentioned in https://stackoverflow.com/a/9177264/6671004. This query does work in mySQL but not in Android (SQLite)

Here's the line of code :

database.execSQL("UPDATE myTable SET id = (id + 1) where id > 2 ORDER BY id desc");

Here's the error I'm getting on Android Studio (Compile time) : https://i.sstatic.net/1k4XD.jpg

This is the exception I'm getting if I remove 'ORDER BY id DESC' from the query :

java.lang.RuntimeException: Unable to start activity ComponentInfo{...}: android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: myTable.id (code 1555)

Is this the correct way to do this? Or is there a better way?

Answer 1

As pointed out by many, this is definitely not the correct way to go.

But I found workaround in case someone else is looking for a similar implementation.

Here it is :

UPDATE myTable SET id = - (id + 1) WHERE id > 1;
UPDATE myTable SET id = - id WHERE id < 0;

This is a hack which I found here.

Again, this is not the correct way to go. But just posting a working solution I found.