Insert or update in SQlite and Android using the database.query();

Question

is there a way to change my function:

public categorie createCategoria(String categoria) {
            ContentValues values = new ContentValues();
            values.put(MySQLiteHelper.COLUMN_NOME, categoria);
            values.put(MySQLiteHelper.COLUMN_PREF, 0);

            long insertId = database.insert(MySQLiteHelper.TABLE_CATEGORIE, null,
                values);

            Cursor cursor = database.query(MySQLiteHelper.TABLE_CATEGORIE,
                allCategorieColumns, MySQLiteHelper.COLUMN_ID + " = " + insertId, null,
                null, null, null);
            cursor.moveToFirst();
            categorie newCategoria = cursorToCategorie(cursor);
            cursor.close();
            return newCategoria;
          }

this is a raw insert, i would like to change this function to make it update or insert accordingly. i would like to change this becouse i'm already using this function in some places, but now i need to choose if insert a row or update (or ignoring the insert) a row with the same COLUMN_NOME. can someone help me doing this?

i mean i would like to insert a new row ONLY if there isn't another with the same name (as usual you know).

score 35 · Answer 1 · answered Aug 11 '15 at 21:19

35

You can use insertWithOnConflict() if you want to insert or update, depending in whether the record exists or not:

SQLiteDatabase db = this.getWritableDatabase();

ContentValues contentValues = new ContentValues();
contentValues.put(COLUMN_ID, id);
contentValues.put(COLUMN_VALUE, value);

// this will insert if record is new, update otherwise
db.insertWithOnConflict(TABLE, null, contentValues, SQLiteDatabase.CONFLICT_REPLACE);

answered Aug 11 '15 at 21:19

lenooh

10,364
5
58
49

5

"Replace" is **not** the same as "update" in some contexts. Imagine if you only want to replace only those fields that are present in the given `ContentValues`. This can happen in cases where web APIs return partial objects (I'm facing this problem myself, today). – Vicky Chijwani Oct 08 '16 at 23:23

score 15 · Accepted Answer · answered Jan 03 '13 at 16:53

15

you could call int nRowsEffected = database.update(...); if there are no rows effected by the update either the row doesn't exist (or you hosed your update()!) therefore you need to call database.insert(...). of course if nRowsEffected > 0 then you are done.

answered Jan 03 '13 at 16:53

David M

2,511
1
15
18

thanks, this is the way i chose, becouse i can change the args if i have to insert, since i have to put some default values. – Matteo Bononi 'peorthyr' Jan 04 '13 at 08:08

score 8 · Answer 3 · answered Jan 03 '13 at 16:53

8

You can use execSQL and use INSERT OR REPLACE

String[] args = {"1", "newOrOldCategory"}; // where 1 is the category id
getWritableDatabase().execSQL("INSERT OR REPLACE INTO table_name (idColoumn, categoryColumn) VALUES (?, ?)", args);

answered Jan 03 '13 at 16:53

ashokgelal

80,002
26
71
84

1

thanks, i guess this is the clean solution, but i have to change arguments if it is an insert instead of update, by the way, this "insert or replace" is a really good thing :D – Matteo Bononi 'peorthyr' Jan 04 '13 at 08:09
3

Careful, insert or replace may be different from what you expect from insert or update. See my answer here: http://stackoverflow.com/a/4253806/365596 – gregschlom Mar 06 '13 at 19:45

score 1 · Answer 4 · answered Aug 08 '15 at 09:44

First of all you have write function which is check whether id is exists in particular Table like:

   /**
     * @param table_name
     * @param server_id
     * @return
     */
    public boolean isServerIdExist(String table_name, int server_id) {
        long line = DatabaseUtils.longForQuery(mDB, "SELECT COUNT(*) FROM " + table_name + " WHERE id=?",
                new String[]{Integer.toString(server_id)});
        return line > 0;
    }

You have to pass table_name and id in that like

   /**
     * INSERT in TABLE_ACCOUNT_DEVICE
     **/
    public long insertOrUpdateAccountDevice(int server_id, int account_id,
                                            String device_name, String device_id,
                                            String last_active, String itp,
                                            String utp, int status) {

        ContentValues values = new ContentValues();

        values.put(ACCOUNT_DEVICE_ACCOUNT_ID, account_id);
        values.put(ACCOUNT_DEVICE_DEVICE_NAME, device_name);
        values.put(ACCOUNT_DEVICE_DEVICE_ID, device_id);
        values.put(ACCOUNT_DEVICE_LAST_ACTIVE, last_active);
        values.put(ACCOUNT_DEVICE_ITP, itp);
        values.put(ACCOUNT_DEVICE_UTP, utp);
        values.put(ACCOUNT_DEVICE_STATUS, status); // 0=pending, 1=active, 2=Inactive, -1=not_found

        /**
         * isServerIdExists
         */
        if (isServerIdExists(TABLE_ACCOUNT_DEVICE, server_id)) {
            values.put(ACCOUNT_DEVICE_SERVER_ID, server_id);
            return mDB.insert(TABLE_ACCOUNT_DEVICE, null, values);
        } else {
            return mDB.update(TABLE_ACCOUNT_DEVICE, values, ACCOUNT_DEVICE_SERVER_ID + " =? ",
                    new String[]{Integer.toString(server_id)});
        }
    }

Hope it will helps you.

Insert or update in SQlite and Android using the database.query();

4 Answers4

Linked