SQLite(Android):使用ORDER BY更新查询 [英] SQLite (Android) : UPDATE query with ORDER BY

问题描述

Android，SQLite:我想在Android中使用SQLite 在myTable的其他行之间插入行.为此，我尝试增加从第3行开始的所有行的id，以便可以在位置3插入新行.

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.

myTable的主键是列ID.表格中没有其他限制.

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

我尝试使用 https://stackoverflow.com/a/9177264/6671004 中提到的查询.此查询可以在 mySQL中工作，但不能在Android(SQLite)

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)

这是代码行:

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

这是我在Android Studio上遇到的错误(编译时): https://imgur.com/a/9r0iyAa

Here's the error I'm getting on Android Studio (Compile time) : https://imgur.com/a/9r0iyAa

这是我从查询中删除"ORDER BY id DESC"时遇到的异常:

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

java.lang.RuntimeException:无法启动活动ComponentInfo {...}:android.database.sqlite.SQLiteConstraintException:UNIQUE约束失败:myTable.id(代码1555)

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?

推荐答案

许多人指出，这绝对不是正确的方法.

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.

是这里:

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

这是我在此处发现的黑客.

再次，这不是正确的方法.但是只是发布我发现的有效解决方案.

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

这篇关于SQLite(Android):使用ORDER BY更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！