Android SQLite update columns only if NULL or empty

Question

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.

forpas · Accepted Answer · 2022-08-17 21:12:45Z

1

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);

answered Aug 17, 2022 at 21:12

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sami Kuhmonen · Accepted Answer · 2022-08-17 19:55:33Z

1

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

edited Aug 17, 2022 at 19:55

answered Aug 17, 2022 at 19:52

Sami Kuhmonen

31.4k9 gold badges67 silver badges81 bronze badges

4 Comments

Darksymphony Over a year ago

and will it work also if the column is empty? I mean it has empty string value "". Or IFNULL will check only if the column value is "NULL"

Sami Kuhmonen Over a year ago

@Darksymphony Added a case where also empty string is updated

Darksymphony Over a year ago

thank you, I updated my question with the solution you provided, also for other columns, but seems, the columns are not updated, are still empty

Darksymphony Over a year ago

ok, so it works only with db.update: db.update(TABLE_NAME, contentValues,COL_7 + " = '" + kateg + "' AND " + COL_10 + " IS NULL OR " + COL_10 + "= ''", null);

Collectives™ on Stack Overflow

Android SQLite update columns only if NULL or empty

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related