Search code examples
javaandroidsqliteandroid-sqlitesqliteopenhelper

Update with WHERE clause JOIN another table

I have the following table:

HABIT 

ID INT PRIMARY KEY

NUMBER INT FOREIGN KEY

DATE DATE

DONE BOOL

HABIT_DESCRIPTION

NUMBER INT PRIMARY KEY

NAME VARCHAR

DESCRIPTION VARCHAR

I want to make an update on specific row in HABIT where name of HABIT_DESCRIPTION is <some_string>

I tried this: 

ContentValues cv = new ContentValues();
    cv.put(HABIT_COL_DONE, true);
    String whereclause = HABIT_DESCRIPTION_NAME+ "='water'";
    return db.update("HABIT",cv,whereclause,null);
Solution

  • Since SQLite does not support joins in the UPDATE statement, you can use EXISTS:

    String whereclause = 
    "EXISTS (SELECT 1 FROM HABIT_DESCRIPTION WHERE NUMBER = HABIT.NUMBER AND DESCRIPTION = ?)";
String param = "water";
return db.update("HABIT", cv, whereclause, new String[] {param});