4

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

Community
  • 1
  • 1
erdomester
  • 11,491
  • 31
  • 126
  • 226

1 Answers1

3

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.

Nelson Osacky
  • 589
  • 2
  • 12