android.database.sqlite.SQLiteException: near "ON": syntax error (code 1): , while compiling: INSERT INTO

Question

It would be helpful if someone could tell me correct syntax for updating tables in sqlite. I want to update table using sqlite but I am getting the following error:

android.database.sqlite.SQLiteException: near "ON": syntax error (code 1): , while compiling: INSERT INTO cost (service_type,cost) VALUES('1000','Oil and Filter Change') ON DUPLICATE KEY UPDATE service_type='Oil and Filter Change',cost='1000';

Code:

public void UpdatePrice(String ser_type, String cost)
{
    String query = "INSERT INTO " + TABLE_COST +
            " (" + COLUMN_SERVICE_TYPE + "," + COLUMN_COST +") VALUES('"+
            cost + "','" +ser_type +"') ON DUPLICATE KEY UPDATE "+
            COLUMN_SERVICE_TYPE + "='" + ser_type + "',"+
            COLUMN_COST + "='" + cost + "';";

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(query, null);
    // Move to first row
    cursor.moveToFirst();

    cursor.close();
    db.close();
}

SQLite doesn't support that syntax ([see here](http://sqlite.org/lang_insert.html)). `INSERT OR REPLACE` _may_ do what you want, but be aware that replace means replace, and the rowid may change. You also probably ought to be using parameterised queries, but that's another matter. — TripeHound, Mar 21 '17 at 14:18

score 0 · Answer 1 · edited May 23 '17 at 12:10

0

ON DUPLICATE KEY UPDATE isn't a valid syntax of SQLite as of now. But since you're interested, you can perhaps achieve the same effect with different sytax described in here.

edited May 23 '17 at 12:10

Community

1
1

answered Mar 21 '17 at 14:18

fluffyBatman

6,524
3
24
25

android.database.sqlite.SQLiteException: near "ON": syntax error (code 1): , while compiling: INSERT INTO

1 Answers1