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

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). 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
– TripeHound, Commented Mar 21, 2017 at 14:18

Community · Accepted Answer · 2017-05-23 12:10:15Z

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, 2017 at 12:10

CommunityBot

11 silver badge

answered Mar 21, 2017 at 14:18

fluffyBatman

6,7123 gold badges27 silver badges25 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related