CODEWITHSUNDEEP
sqliteupsert
chiapa
chiapa

Reputation: 4412

Inserting or updating SQLite table depending on id

I have a SQLite table like this:

CREATE TABLE test (id int PRIMARY KEY, Name text, Country text, ..., ...)

I am getting data for this table that can have the column values changed, except for the id. So, I want to insert if there isn't a specific id in there, however, I want to update the record with that id with the new values.

I have tried this:

INSERT OR IGNORE INTO test (id, Name, ..., ...) VALUES (?,?, ....)"

This doesn't create duplicate id's - which is fine - but it doesn't update the rest of the record either if the id already exists in the table.

I want to avoid using SELECT for that id and check if it is there, then update or insert accordingly.

Upvotes: 0

Views: 264

Answers (2)

CL.
CL.

Reputation: 180280

You could just try the UPDATE, and if no record was actually updated, execute the INSERT:

ContentValues cv = new ContentValues();
cv.put("Name", ...);
if (db.update("test", cv, "id = " + myID, null) < 1) {
    cv.put("id", myID);
    db.insert("test", null, cv);
}

Upvotes: 1

redneb
redneb

Reputation: 23920

You could try to use INSERT OR REPLACE. This will try to insert the row first and if there is conflict with the id, it will delete the old row and try the insert once again.

INSERT OR REPLACE has the disadvantage that you have to specify all fields of the row for every "upsert" operation. If this does not work for you, then the only alternative is to do a SELECT followed by an INSERT or an UPDATE. You did say that you don't like that, but in sqlite this is not as bad as with other RDBMSs; sqlite is an embeded RDBMS and not a client-server RDBMS and therefore the cost associated with doing multiple such operations is much smaller. Don't forget to wrap the two operations in a transaction though.

Upvotes: 1

Related Questions