Android DB Update near "WHERE": syntax error

Question

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

it looks like this is not the code that caused this exception — njzk2
– njzk2, Commented Jul 30, 2015 at 23:55
@njzk2 you're right. I'm so sorry, I should've restarted my computer before asking it here — Margo Eastham
– Margo Eastham, Commented Jul 31, 2015 at 0:08

Rahul · Accepted Answer · 2015-07-30 23:14:32Z

2

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

edited Jul 30, 2015 at 23:14

answered Jul 30, 2015 at 23:12

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Margo Eastham Over a year ago

I figured that too. But my third argument is : "name=? AND expired=?". I don't know why but the AND is not showing in the error message

Germo · Accepted Answer · 2015-07-30 23:47:44Z

0

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.

answered Jul 30, 2015 at 23:47

Germo

454 bronze badges

4 Comments

njzk2 Over a year ago

why would you do that? why would you suggest to not use the tools at our disposal? and more to the point, why would you think it would work any better? (fyi, your queries obviously fail because food.name is a string)

Germo Over a year ago

Because it's obviously not working properly, right? Besides, if he can't make it work even with a raw query, then he knows problem is not in the statement, but in the vars. Hum, yeah? food.name is a string. Functions expects string... what's the problem?

njzk2 Over a year ago

exactly. it's not working properly, yet nothing seems wrong, and looking at the error tells us that the code is not related to the error. There is no point in fiddling around trying different methods of obtaining the same result if what the error is is not clear in the first place

Germo Over a year ago

Alright, you have a point there. But I still think that trying other methods of getting the same result, and getting the same exception in return does help to find that the problem was with the compiler/emulator

Collectives™ on Stack Overflow

Android DB Update near "WHERE": syntax error

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related