2

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.stack.imgur.com/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?

Shrey Garg
  • 1,317
  • 1
  • 7
  • 17
  • `Is this the correct way to do this?` **NO**. The `ORDER BY` clause works on *queries* (`SELECT ...`), not on *commands*. You should not care about the sorting order when inserting or updating data, only at presentation time, if that matters. – Phantômaxx Feb 18 '19 at 12:28
  • Your first mistake is trying to use the rowid to determine ordering at all. Don't do that. – Shawn Feb 18 '19 at 12:33
  • @Fantômas I agree, but I have a scenario where this seems to be the best option, is there any way to make this work? – Shrey Garg Feb 18 '19 at 12:38
  • As per my understanding every insert/update operation will take a internal lock in DB for other write also some time for read, and untill transaction complete other read can't get the update, so order in which update is done internally from sqlite should not effect. But error given here is because you have made the "id" as unique field and update that with already existing id value will through constraint error. – Deepak Feb 18 '19 at 14:02

3 Answers3

4

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.

Shrey Garg
  • 1,317
  • 1
  • 7
  • 17
1

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 :-

enter image description here

  • 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.

MikeT
  • 51,415
  • 16
  • 49
  • 68
  • SQLite now supports ORDER BY and LIMIT clauses: https://sqlite.org/lang_update.html - 2.3 – Ryan Nov 11 '20 at 03:28
  • 1
    @Ryan The ORDER BY does not affect the order in which the rows are updated though. Here's a quote from that link you provided: "The ORDER BY clause on an UPDATE statement is used only to determine which rows fall within the LIMIT. The order in which rows are modified is arbitrary and is not influenced by the ORDER BY clause.". That's very important to understand. – Mickael Bergeron Néron Feb 08 '22 at 03:14
0

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);
Jon
  • 79
  • 7
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 02 '22 at 03:31