CODEWITHSUNDEEP
androidsqlitesql-update
Margo Eastham
Margo Eastham

Reputation: 655

Android DB Update near "WHERE": syntax error

I know there are already too many questions about this, and believe me I've done my part reading the answers to find a solution. Unfortunately, I can't find my mistake

Here's my code:

SQLiteDatabase db = getWritableDatabase();

ContentValues values = new ContentValues();
values.put("eat_it", 1);
values.put("expired", 1);
String[] args = {food.name, "0"};

db.update("foods", values, "name=? AND expired=?", args);
db.close();

What I want to do:

Find a record in the table foods WHERE the value of column name = food.name and the value of column expired = 0. If found, SET the record's eat_it column to 1 and expired column to 1. If no record found, don't do anything

Here's my create table syntax:

CREATE TABLE IF NOT EXISTS foods (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    ...
    eat_it INTEGER DEFAULT 0,
    expired INTEGER DEFAULT 0
);

Here's the error message:

...
 Caused by: android.database.sqlite.SQLiteException: near "WHERE": syntax error (code 1): , while compiling: UPDATE foods SET expired=?,eat_it=? WHERE name ='Taco Salad'WHERE expired = 0
...

Thanks for your help

====UPDATE====

Apparently after I restart my computer it worked. I think my emulator was not working properly/ still has the old code. Thanks everyone for helping

Upvotes: 1

Views: 713

Answers (2)

Germo
Germo

Reputation: 45

You can use the where statement without using the 'whereArgs' like this:

db.update("foods", values, "name="+food.name+" AND expired=0", null);

Also, since you're updating internal values, you can also try a simple query:

db.rawquery("UPDATE foods SET eat_it=1, expired=1 WHERE name="+food.name+" AND expired=0");

Although there should be nothing wrong with what you wrote there, you can try those two options to make it work.

Upvotes: 0

Rahul
Rahul

Reputation: 77876

Error message returned per your post

near "WHERE": syntax error (code 1): , while compiling: UPDATE foods SET expired=?,eat_it=? WHERE name ='Taco Salad'WHERE expired = 0

It's clear from the error statement that you have two WHERE clause in your query and so the error.

UPDATE foods SET expired=?,eat_it=? 
WHERE name ='Taco Salad'
WHERE expired = 0 <-- Here

Your UPDATE statement should look like

UPDATE foods SET expired=?,eat_it=? 
WHERE name ='Taco Salad'
AND expired = 0

Upvotes: 2

Related Questions