Android SQLite仅在NULL或为空时更新列

Question

在android中，我试图更新SQLite数据库中的一个表，但我想实现的是，它只更新空列或NULL值的列。

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

因此，如果COL_10为空或具有NULL值，那么我需要用新值更新它。否则，如果COL_10中已经有一个值，则不对该列执行任何操作。COL11和COL12也是如此。

我想在这行中添加另一个条件，比如：

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

甚至我想先从其他查询中获取值：

String Query2 = "SELECT * FROM "+ TABLE_NAME ";
Cursor cursor2 = db.rawQuery(Query2, null);
while (cursor2.moveToNext()) {
output_col10 = cursor2.getString(cursor2.getColumnIndexOrThrow(COL_10));

然后检查CCD_ 1是否为空或者长度>0，但我认为这太夸张了。

有没有可能在我的db.update函数中简化它？

根据上一个答案，我更新了它：

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

但是由于某些原因，这些列仍然是空的并且没有用任何值更新。

Answer 1

您可以使用单个UPDATE语句和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);

Answer 2

如果您想逐列进行，可以使用IFNULL来执行此操作：

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

如果col1为null，这将把它设置为"somevalue"，否则它将保持其当前值，而不管彼此的null状态如何，col2也是如此。

您可能需要添加CASE/WHEN语句来满足更新内部空字符串的要求，因此只使用它可能更容易：

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