删除使用SQLite行，其中有多个条款条款 [英] Delete SQLite Row with where clause with multiple clauses

问题描述

我一直在寻找周围，而不是设法找到一个解决方案或工作的例子所以这里去。

我已经建立了具有不同的领域，有效地建立了用户列表，五列SQLite数据库。将有属于同一列表的多个值，保存其设置为自动增量的ROWID。

无论是用户还是程序都知道的ROWID一旦一个数值已经输入到数据库中，所以我希望用户能够删除一行，如果它包含了其他领域的所有四个。

我的code是这样的：

 数据库
            .delete（DATABASE_TABLE，
                    KEY_DATE ='日期'AND KEY_GRADE ='蓝紫魅力'AND KEY_STYLE ='风格'AND KEY_PUMPLEVEL ='pumpLevel'
                    空值）;
 

但是，这不会删除任何值。我几乎可以肯定，这个命令的语法是难辞其咎的。

我如何格式化具有多个delete命令的where子句？

where子句

从下面的解决方案：

  ourDatabase.delete（DATABASE_TABLE，ROWID =（SELECT MAX（ROWID）FROM
            + DATABASE_TABLE +WHERE+号=？AND级=？AND
            +的风格=？AND pump_level =？），新的String [] {日期，等级，
            风格，pumpLevel}）;
 

解决方案

我有一种感觉， KEY_DATE ， KEY_GRADE 等都是你的Java变量名的不的实际列名。试着改变你的code这样：

  .delete（DATABASE_TABLE，
        KEY_DATE +=日期和+ KEY_GRADE +=蓝紫魅力和+
        KEY_STYLE +=风格和+ KEY_PUMPLEVEL +=pumpLevel'
        空值）;
 

此外，我认为'日期'，风格等存储在本地变量，你应该使用 whereArgs 参数从 SQL注入攻击自己的项目的：

  .delete（DATABASE_TABLE，
        KEY_DATE +=？和+ KEY_GRADE +=？和+
        KEY_STYLE +=？和+ KEY_PUMPLEVEL +=？，
        新的String [] {日期，档次，风格，pumpLevel}）;
 

（其中日期=日期，级=蓝紫魅力等）

---

从添加注释

要删除最后一行用这样的：

  .delete（DATABASE_TABLE，
        ROWID =（SELECT MAX（ROWID）FROM+ DATABASE_TABLE +），
        空值）;
 

---

要删除您的最后匹配行试试这个：

  .delete（DATABASE_TABLE，
        ROWID =（SELECT MAX（ROWID）FROM+ DATABASE_TABLE +WHERE+
            KEY_DATE ='日期'AND KEY_GRADE ='蓝紫魅力和+
            KEY_STYLE ='风格'AND KEY_PUMPLEVEL ='pumpLevel'），
        空值）;
 

不过看到我对SQL注入攻击第二个音符，以保护您的数据。

I've been searching around and not managed to find a solution or working example so here goes.

I've set up an SQLite database which has five columns with different fields which effectively builds a list for the user. There will be multiple values in the list that are the same, save for the ROWID which is set to auto increment.

Neither the user nor the program will know the ROWID once a value has been entered into the database, so I want for the user to be able to remove one row if it contains all four of the other fields.

My code looks like this:

Database
            .delete(DATABASE_TABLE,
                    "KEY_DATE='date' AND KEY_GRADE='style2' AND KEY_STYLE='style' AND KEY_PUMPLEVEL='pumpLevel'",
                    null);

But this does not remove any values. I'm almost certain that the syntax of this command is to blame.

How do I format a where clause for delete command with multiple where clauses?

Solution from below:

ourDatabase.delete(DATABASE_TABLE, "ROWID = (SELECT Max(ROWID) FROM "
            + DATABASE_TABLE + " WHERE " + "date=? AND grade=? AND "
            + "style=? AND pump_level=?)", new String[] { date, grade,
            style, pumpLevel });

解决方案

I have a feeling that KEY_DATE, KEY_GRADE, etc are you Java variable names not your actual column names. Try changing your code to this:

.delete(DATABASE_TABLE,
        KEY_DATE + "='date' AND " + KEY_GRADE + "='style2' AND " +
        KEY_STYLE + "='style' AND " + KEY_PUMPLEVEL + "='pumpLevel'",
        null);

Also I assume that 'date', 'style', etc are stored in local variables, you should use the whereArgs parameter to project yourself from SQL Injection Attacks:

.delete(DATABASE_TABLE,
        KEY_DATE + "=? AND " + KEY_GRADE + "=? AND " +
        KEY_STYLE + "=? AND " + KEY_PUMPLEVEL + "=?",
        new String[] {date, grade, style, pumpLevel});

(where date = "date", grade = "style2", etc)

---

Added from comments

To delete the last row use this:

.delete(DATABASE_TABLE,
        "ROWID = (SELECT Max(ROWID) FROM " + DATABASE_TABLE + ")",
        null);

---

To delete your last matching row try this:

.delete(DATABASE_TABLE,
        "ROWID = (SELECT Max(ROWID) FROM " + DATABASE_TABLE + " WHERE " +
            "KEY_DATE='date' AND KEY_GRADE='style2' AND " +
            "KEY_STYLE='style' AND KEY_PUMPLEVEL='pumpLevel')",
        null);

But see my second note about SQL Injection Attacks to protect your data.

这篇关于删除使用SQLite行，其中有多个条款条款的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！