如果不存在的Andr​​oid SQLite的插入记录 [英] Android sqlite insert record if not exists

问题描述

欲新项目（乳酪）添加到源码表，但只在情况下，它不存在。我有两列只在表：_id（KEY_ROWID_PR）和PRODUCT_NAME（KEY_NAME_PR）

我一直在试图与这些code，但它给了我一个错误：

 公共无效populate_base_lists2（字符串表名，字符串项）{
        ourDatabase.execSQL（插入+表名+（+ KEY_NAME_PR +）SELECT * FROM（SELECT+项目+）WHERE NOT EXISTS（SELECT+ KEY_NAME_PR +FROM+表名+WHERE+ KEY_NAME_PR +=+项目+）LIMIT 1;）;
}
 

  

17 03-11：27：20.972：E / AndroidRuntime（658）：产生的原因：
  android.database.sqlite.SQLiteException：没有这样的列：奶酪：
  INSERT INTO PR_Dairy_Products（PRODUCT_NAME）SELECT * FROM（SELECT
  奶酪）WHERE NOT EXISTS（SELECT了PRODUCT_NAME PR_Dairy_Products
  WHERE PRODUCT_NAME =奶酪）LIMIT 1;

在code是从这里开始： MySQL的：插入记录如果不存在于表

没有这样的列：奶酪，好吧，我改变了部分KEY_NAME_PR，那么错误是一样的，但没有这样的列：PRODUCT_NAME。该列绝对存在。

更重要的是，我需要在这个code一些解释。我有一定程度的SQL知识，但我不能让这一点。

感谢

解决方案

我想你可能需要周围的变量单引号的项目的，只要它出现，因为它的值没有列名。
试试这个code：

 公共无效populate_base_lists2（字符串表名，字符串项）{
ourDatabase.execSQL（插入+表名+
（+ KEY_NAME_PR +）SELECT * FROM（SELECT'+项目+'）
WHERE NOT EXISTS（
选择+ KEY_NAME_PR +
FROM+表名+WHERE+ KEY_NAME_PR +='+项目+'
）LIMIT 1;）;
}
 

希望有所帮助。

I want to add a new item (Cheese) to an sqlite table but only in case it does not exist. I have two columns only in the table: _id (KEY_ROWID_PR) and product_name (KEY_NAME_PR).

I've been trying with these code, but it gives me an error:

public void populate_base_lists2(String tablename, String item) {
        ourDatabase.execSQL("INSERT INTO " + tablename + " (" + KEY_NAME_PR + ") SELECT * FROM (SELECT " + item  + ") WHERE NOT EXISTS (SELECT " + KEY_NAME_PR + " FROM " + tablename + " WHERE " + KEY_NAME_PR + " = " + item + ") LIMIT 1;");
}

03-11 17:27:20.972: E/AndroidRuntime(658): Caused by: android.database.sqlite.SQLiteException: no such column: Cheese: INSERT INTO PR_Dairy_Products (product_name) SELECT * FROM (SELECT Cheese) WHERE NOT EXISTS (SELECT product_name FROM PR_Dairy_Products WHERE product_name = Cheese) LIMIT 1;

The code is from here: MySQL: Insert record if not exists in table

no such column: Cheese, ok, I changed that part to KEY_NAME_PR, then the error was the same, but with no such column: product_name. That column definitely exists.

What is more, I need some explanation on this code. I have sql knowledge on a certain level, but I cannot get this.

Thanks

解决方案

I think you may need single quotes around the variable item whenever it appears because it a value not a column name. Try this code:

public void populate_base_lists2(String tablename, String item) {
ourDatabase.execSQL("INSERT INTO " + tablename +
" (" + KEY_NAME_PR + ") SELECT * FROM (SELECT '" + item  + "')
WHERE NOT EXISTS (
SELECT " + KEY_NAME_PR +
" FROM " + tablename + " WHERE " + KEY_NAME_PR + " = '" + item + "'
) LIMIT 1;");
}

Hope that helps.

这篇关于如果不存在的Andr​​oid SQLite的插入记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！