CODEWITHSUNDEEP
sqlite
Gianluca Ghettini
Gianluca Ghettini

Reputation: 11628

SQLite INSERT OR REPLACE with condition

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?

Upvotes: 2

Views: 427

Answers (1)

Gatunox
Gatunox

Reputation: 532

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;
}

Upvotes: 2

Related Questions