SQLite upsert for android

Question

i have create next table:

final String SQL_CREATE_TABLE_NEWS_TAPE = "CREATE TABLE " + NewsTapeEntry.TABLE_NAME +
                " (" + NewsTapeEntry.NEWS_ID + " INTEGER PRIMARY KEY, " +
                NewsTapeEntry.USER_ID + " INTEGER NOT NULL, " +
                NewsTapeEntry.CONTENT + " TEXT, " +
                NewsTapeEntry.CREATED_AT + " TEXT, " +

                " FOREIGN KEY (" + NewsTapeEntry.NEWS_ID + ") REFERENCES " +
                NewsTapeEntry.TABLE_NAME + " (" + NewsTapeEntry.NEWS_ID + "), " +
                " UNIQUE (" + NewsTapeEntry.NEWS_ID + ") ON CONFLICT UPDATE);";

but if i make update myNews, it is every time insert each row in this table... And I need the next result: when its updating - firs check if current ID exist, if true - update current row in table, if false - make insert! But there is small point, that must be done: if ID exist, and row updating, it must not overwrite the empty fields with default value. For example: i am updating myNews, i receive the answer with existed NEWS_ID, but in this answer are not consist field CREATED_AT. so after updating the row field CREATED_AT must be previous(not updated and not replaced by default value).

will be glad any solutions!

UPDATE

when i finish receive new data, i am doing bulckIncert:

getContentResolver().bulkInsert(Contract.NewsTapeEntry.CONTENT_URI, mCvArray);

@DerGol...lum, looks like it is doesn't check if ID already exist... or i am missing something? — Stan Malcolm, Jul 14 '15 at 09:43
Might not be applicable, but [my answer to a question about updating one table from another](http://stackoverflow.com/a/22481731/2096401) by using triggers may be an alternative approach. — TripeHound, Jul 14 '15 at 09:44
I'm using REPLACE to insert new records. If the record already exists, then it gets updated. As an alternative, you could use the old pattern: run a SELECT to check if the record exists: if not, exec an INSERT. Else, exec an UPDATE. Maybe less performing than a REPLACE. — Phantômaxx, Jul 14 '15 at 09:58
@DerGol...lum in fact i don't need to update all row, but only that fields that come with new updates, and other fields must must stay the same — Stan Malcolm, Jul 14 '15 at 10:29
... Which is what you normally do in an UPDATE, too. Nothing changes. — Phantômaxx, Jul 14 '15 at 10:40
@DerGol...lum, can you write an example how to use SELECT insede bulkInsert, please? or in this case i need to use regular INSERT? — Stan Malcolm, Jul 14 '15 at 11:03
No, but you might find this reading useful: https://www.buzzingandroid.com/2013/01/sqlite-insert-or-replace-through-contentprovider/ — Phantômaxx, Jul 14 '15 at 11:12

SQLite upsert for android

0 Answers0