Using insertWithOnConflict for update or insert

android sqlite where-clause insert-update
Marian Klühspies picture Marian Klühspies · Mar 25, 2014 · Viewed 10.2k times · Source

I need to insert or update and I found the method insertWithOnConflict of the SQLiteDatabase, but I do not know how it checks if the entry already exists.

Theoretically I need a "Where" argument to check if a certain ID exists, and if so, it should replace all other columns.

This is what I have now, but I don´t think that the second argument is the unique id

    ContentValues args = new ContentValues();
    args.put(AppDatabase.COLUMN_ID, entry.getId());
    args.put(AppDatabase.COLUMN_NAME, entry.getAppname());
    args.put(AppDatabase.COLUMN_URL, entry.getAppUrl());
    database.insertWithOnConflict(AppDatabase.TABLE_FILELIST, COLUMN_ID, args, SQLiteDatabase.CONFLICT_REPLACE);

How can I manage this behaviour?

Answer

laalto picture laalto · Mar 25, 2014

  1. Make sure you have some appropriate constraint in your table, such as PRIMARY KEY or UNIQUE.

  2. When inserting, add a value to this constrained column via ContentValues. If inserting the new row would violate some constraint, the conflicting rows are first deleted and then the new row is inserted.

In your case, COLUMN_ID looks like a good candidate for PRIMARY KEY constraint. The second arg nullColumnHack with value COLUMN_ID in your code is not necessary, you can pass it as null.

Related questions

Delete SQLite Row with where clause with multiple clauses

I've been searching around and not managed to find a solution or working example so here goes. I've set up an SQLite database which has five columns with different fields which effectively builds a list for the user. There will …

Read More
SQLite in Android How to update a specific row

I've been trying to update a specific row for a while now, and it seems that there are two ways to do this. From what I've read and tried, you can just use the: execSQL(String sql) method or the: …

Read More
How to delete all records from table in sqlite with Android?

My app has two buttons, the first button is for deleting record on user input and the second button is for deleting all records. But when I want to delete data it shows the message "Your application has been forcefully …

Read More