Android SQLite update columns only if NULL or empty-CodePudding

In android I am trying to update a table in SQLite database, but I want to achieve, that it will update only empty columns or columns with NULL value.

public void fillCal(String swin, String swout, String ripout, String kateg) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();

    contentValues.put(COL_10, swin);
    contentValues.put(COL_11, swout);
    contentValues.put(COL_12, ripout);

    db.update(TABLE_NAME, contentValues,COL_7   " = '"   kateg   "'", null);db.close();
}

So if COL_10 is empty or has a NULL value, then I need to update it with a new value. Otherwise if there is already a value in COL_10, do nothing with that column. The same for COL11 and COL12.

I thought to add another conditions in this line, like:

db.update(TABLE_NAME, contentValues,COL_7   " = '"   kateg   "' AND COL10!='' AND COL11!='' AND COL12!=''", null);db.close();

or even I wanted first to get the values from other query:

 String Query2 = "SELECT * FROM "  TABLE_NAME ";

        Cursor cursor2 = db.rawQuery(Query2, null);
        while (cursor2.moveToNext()) {
        output_col10 = cursor2.getString(cursor2.getColumnIndexOrThrow(COL_10));

and then check if output_col10 is null or length>0, but this is an overkill I think.

Is there a possibility to do it simplier in my db.update function?

According to last answer I updated it :

public void fillCal(String swin, String swout, String ripout, String kateg) {

        SQLiteDatabase db = this.getWritableDatabase();
        String Query = "UPDATE "   TABLE_NAME   " SET "   COL_10   " = CASE WHEN "   COL_10   " IS NULL OR "   COL_10   " = '' THEN '"   swin   "' ELSE "   COL_10   " END"   " where "   COL_7   " = '"   kateg   "'";
        Cursor cursor = db.rawQuery(Query, null);
        cursor.close();
   
        String Query1 = "UPDATE "   TABLE_NAME   " SET "   COL_11   " = CASE WHEN "   COL_11   " IS NULL OR "   COL_11   " = '' THEN '"   swout   "' ELSE "   COL_11   " END"   " where "   COL_7   " = '"   kateg   "'";
        Cursor cursor1 = db.rawQuery(Query1, null);
        cursor1.close();

        String Query2 = "UPDATE "   TABLE_NAME   " SET "   COL_12   " = CASE WHEN "   COL_12   " IS NULL OR "   COL_12   " = '' THEN '"   ripout   "' ELSE "   COL_12   " END"   " where "   COL_7   " = '"   kateg   "'";
        Cursor cursor2 = db.rawQuery(Query2, null);

        cursor2.close();
        db.close();
    }

but for some reason the columns are still empty and not updated with any value.

CodePudding user response：

You can update all the columns with a single UPDATE statement and execSQL():

String query = "UPDATE "   TABLE_NAME   " SET "   
               COL_10   " = CASE WHEN "   COL_10   " IS NULL OR "   COL_10   " = '' THEN '"   swout   "' ELSE "   COL_10   " END,"   
               COL_11   " = CASE WHEN "   COL_11   " IS NULL OR "   COL_11   " = '' THEN '"   swout   "' ELSE "   COL_11   " END,"   
               COL_12   " = CASE WHEN "   COL_12   " IS NULL OR "   COL_12   " = '' THEN '"   swout   "' ELSE "   COL_12   " END "    
               "WHERE "   COL_7   " = '"   kateg   "'";
db.execSQL(query);

CodePudding user response：

You can use IFNULL to do this if you want to go column by column:

UPDATE table SET col1=IFNULL(col1, ‘somevalue’), col2=IFNULL(col2, ‘othervalue’)

This will set col1 to ‘somevalue’ if it’s null, otherwise it will keep its current value, and same for col2 regardless of each other’s null state.

You may need to add CASE/WHEN statements for the requirement of updating empty string inside, so it might be easier to just use that:

UPDATE table SET col1=CASE WHEN col1 IS NULL OR col1=‘’ THEN ‘somevalue’ ELSE col1 END