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://imgur.com/a/9r0iyAa

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.

Answer 2

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)

That question is tagged MYSQL. MYSQL has many differences from SQLite.

Here's the line of code :

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

The SQLite UPDATE SQL takes the form of :-

• i.e. there is no ORDER BY clause and hence the error saying that if you're going to use any KEYWORD then it must be a BETWEEN or IN or a ; (of course you could also extend the condition using AND OR and so on).

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

The reason being is that the rowid (id being an alias of rowid) has an implied UNIQUE constraint and that the rows will be updated according to the id column in order. So if there are more than 3 rows (or have been and the last row has never been deleted) then when the id is 3, a row with 4 will exist and 3 + 1 = 4 so the row already exists and hence the UNIQUE constraint being encountered.

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.

In short that is not a good idea and is virtually definitely not needed.

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

Definitely no

At a guess you want a nice humanly understandable value so you can know what's going on. For example you may present a list with the sequence so you can then say delete the row that has a sequence of 3 and thus equate that to the id column. Fine until you present the list in a different order which may be more user friendly. Suddenly your sequence becomes complicated or even useless and if displayed confusing.

identifiers are intended to identify a row and allow fast access to that row as a numerical index will be more efficient (than a human easily readable non-numeric index) to process. They also cater for reducing unnecessary additional processing (shifting data).

An efficient methodology is presenting the list with tying the id to the position in the list (so position could be the nth element of an array that holds the respective id, regenerating the list (the underlying array) according to a different order so the nth element will still have the respective id).

Embarking on a process of shifting rows will impose additional resource usage for example extra disk IO whose cost is relatively expense. This will be compounded by the fact that you will have to process the rows in reverse order to get around the UNIQUE constraint, that in itself is going to require using even costlier methods because SQLite will by default try to perform the updates efficiently rather than cater for the efficiencies required to digress from recognised/accepted techniques that utilise the underlying efficiencies.

Answer 3

I found this one working. And remove autoincrement from id

String strSQL1 = "UPDATE people_table SET id = (id +1) WHERE id < 0";
String strSQL = "UPDATE people_table SET id = (id -1) WHERE id > 1";
db.execSQL(strSQL);
db.execSQL(strSQL1);