我的sqlite3 CASE语句有什么问题? [英] What is wrong with my sqlite3 CASE statement?

问题描述

我的代码如下:

SELECT COUNT(_id) AS count FROM general WHERE _id = 1 CASE WHEN count > 0 THEN UPDATE general SET userGivenId = 'xxx' WHERE _id = 1 ELSE INSERT INTO general (userGivenId) VALUES ('xxx' ) END

出现错误:

android.database.sqlite.SQLiteException: near "CASE": syntax error: , while compiling: SELECT COUNT(_id) AS count FROM general WHERE _id = 1 CASE WHEN count > 0 THEN UPDATE general SET userGivenId = 'xxx' WHERE _id = 1 ELSE INSERT INTO general (userGivenId) VALUES ('xxx' ) END

这是我将使用的最短查询.之所以这样做，是因为我的其他查询将包含需要更新的行，但有些行可能未被触及.使用replace将替换所有数据(至少这对我的Android手机有效).例如，我的File类将具有一个filePath，但是有时服务器的响应将返回null，并且如果服务器返回一个新的File，我只是要更新File.

This is the shortest query I will use. Why I do this is because my other queries will have rows that needs to be updated but some may not be touched. Using replace will replace all the data (at least that is how it works for me on my Android phone). For instance my File class will have a filePath, but sometimes the response from the server will return null and I am only to update the File IF the server returns a new File.

我忘了写东西吗?

推荐答案

SQLite没有任何控制逻辑，因为这在嵌入式数据库中是没有意义的(没有单独的服务器机器/进程可以异步执行以提高性能) . CASE只能用于表达式，不能用于整个命令.

SQLite does not have any control logic because this would not make sense in an embedded database (there is no separate server machine/process whose asynchronous execution could improve performance). CASE can be used only for expressions, not for entire commands.

处理应用程序中的控制逻辑:

Handle the control logic in your app:

Cursor c = db.rawQuery("SELECT 1 FROM general WHERE _id = 1", null);
if (c.moveToFirst())
    db.execSQL("UPDATE general SET userGivenId = 'xxx' WHERE _id = 1");
else
    db.execSQL("INSERT INTO general (userGivenId) VALUES ('xxx')");

对于这些特定命令，如果在_id列上具有唯一约束(并且主键被约束为唯一)，则可以使用

For these particular commands, if you have a unique constraint on the _id column (and a primary key is constrained to be unique), you can use the INSERT OR REPLACE command:

INSERT OR REPLACE INTO general(_id, userGivenId) VALUES(1, 'xxx')

这篇关于我的sqlite3 CASE语句有什么问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！