SQLite INSERT OR REPLACE with condition

Question

I have a SQLite database and I'm inserting new records using this query:

INSERT OR REPLACE INTO tags (id, name, color, type, deleted, dirty) VALUES (?, ?, ?, ?, ?, 0)

Id is PRIMARY KEY

If I'm inserting a new record (id doesn't exists in the table) INSERT get executed and everything is fine. If the id already exists then the REPLACE kicks in and the record get replaced. I should add a condition to the REPLACE query: the record should be replaced if and only if dirty is set to 1 in the record already present in the table. How can I add such condition to the query?

You're looking for UPSERT, which sadly SQLite does not support directlty. You could do separate updates and inserts. See here for some workarounds: https://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace — Tim Biegeleisen, Sep 17 '17 at 16:56
I'd like to avoid race conditions. Could you please show me a version with multiple queries? — Gianluca Ghettini, Sep 17 '17 at 17:56

score 2 · Accepted Answer · answered Sep 17 '17 at 18:24

This is what i use.

First attempt the update with your condition then, if not updated, Perform the insert...

Check one example below. calling upsertCategory

/*
 * Update a category
 */
public long updateCategory(Category category) {

    ContentValues values = new ContentValues();
    values.put(COL_CATEGORYDESCRIPTION, category.getDescription());
    values.put(COL_CATEGORYSTATUS, category.getStatus());
    values.put(COL_CATEGORYCREATEDAT, category.getCreatedAt().getDate());
    values.put(COL_CATEGORYUPDATEDAT, category.getUpdatedAt().getDate());

    long id = db.update(TABLE_CATEGORIES, values, KEY_ID + "=" + category.getId(), null);

    return id;
}


/*
 * Update or Insert a category
 */
public long upsertCategory(Category category) {

    // update row
    long id = updateCategory(category);

    // 0 rows affected
    if (id == 0) {
        // insert row
        id = insertCategory(category);
    }

    return id;
}

SQLite INSERT OR REPLACE with condition

1 Answers1