How do I update the field of a table with the count of another table? SQLite, Java, Android

Question

Hi all. I'm getting an "Invalid Character Constant" under the " 'D " just after UPDATE. Any Ideas? ORIGINAL :

public Cursor fetchAllJournals(String sort) {

            mDb.rawQuery(UPDATE 'DATABASE_JOURNAL_TABLE'
SET 'KEY_JOURNAL_NOTES' =
(SELECT COUNT(*) FROM 'DATABASE_HOMES_TABLE'
WHERE 'DATABASE_JOURNAL_TABLE'.'KEY_JROWID' = 'DATABASE_HOMES_TABLE'.'KEY_HOME_JOURNALID')
)
return mDb.query(DATABASE_JOURNAL_TABLE, new String[] {KEY_JROWID, 
KEY_JOURNAL_TITLE, KEY_JOURNAL_NOTES, KEY_JDATE},null , null, null, null, sort ,null);
        }

ANSWER:

 public Cursor fetchAllJournals(String sort) {
            mDb.execSQL("UPDATE "+DATABASE_JOURNAL_TABLE+" SET "+KEY_JOURNAL_NOTES+" " +
                    " = (SELECT COUNT(*) FROM "+DATABASE_HOMES_TABLE+" WHERE "
                    +DATABASE_JOURNAL_TABLE+"."+KEY_JROWID+" = "+DATABASE_HOMES_TABLE +"." + KEY_HOME_JOURNALID+")");

            return mDb.query(DATABASE_JOURNAL_TABLE, new String[] {KEY_JROWID, 
                    KEY_JOURNAL_TITLE, KEY_JOURNAL_NOTES, KEY_JDATE},null , null, null, null, sort ,null);
        }

Answer 1

you might start by putting the whole query into double quotation marks (")

Answer 2

The invalid character constant in this case is 'DATABASE_JOURNAL_TABLE'. Single quotes in Java define a character constant, i.e. one character, e.g. 'a' or 'b' or '!'. 'DATABASE_JOURNAL_TABLE' is a string of them so you have to use a String literal and double quotes, i.e. "prefix "+DATABASE_JOURNAL_TABLE+" suffix".

Try this:

public Cursor fetchAllJournals(String sort) {

mDb.rawQuery("UPDATE "+DATABASE_JOURNAL_TABLE+" SET "+KEY_JOURNAL_NOTES+" = (SELECT COUNT(*) FROM "+DATABASE_HOMES_TABLE+"WHERE "+DATABASE_JOURNAL_TABLE+"."+KEY_JROWID+" =+"DATABASE_HOMES_TABLE"+."+KEY_HOME_JOURNALID+")");

return mDb.query(DATABASE_JOURNAL_TABLE, new String[] {KEY_JROWID, KEY_JOURNAL_TITLE, KEY_JOURNAL_NOTES, KEY_JDATE},null , null, null, null, sort ,null);
}