Android SQL update table
I am upgrading my database to add another column. What I am trying to do is (after the column is added in onUpgrade) this method is called from the main activity for each table (3 were upgraded). The method is supposed to replace all of the blanks in the new column with "1".
The code runs fine, stepping through, boolean test is true every time but when I open the table to view the data, the entire column is blank. The weird part is, my rowId numbers are incrementing every time. It starts out with 3 rows with rowIds of 1,2,3 respectively. After my code runs once, they now have rowIds of 4,5,6 respectively.
Can anyone help me out? KEY_ROWID is just my auto rowId number. KEY_MODE is just "mode" for column title. If I run through debugging it, the three rows I have show up in the code (it runs through the while loop 3 times).
public void checkBlanks(String table) {
Cursor cursor = mDb.query(table, new String[] {KEY_ROWID, KEY_MODE}, null, null, null, null, null);
while (cursor.moveToNext()) {
int modeCol = cursor.getColumnIndexOrThrow(KEY_MODE);
if (cursor.isNull(modeCol)) {
int rowId = cursor.getInt(cursor.getColumnIndexOrThrow(KEY_ROWID));
ContentValues args = new ContentValues();
args.put(KEY_MODE, 1); // replace the blank space with a "1"
boolean test = mDb.update(table, args, KEY_ROWID + "=" + rowId, null) > 0;
}
}
cursor.close()开发者_StackOverflow中文版;
}
Instead of manually looping through the rows, why don't you just leverage the power of SQL and update ALL of the rows in one call? E.g.
mDb.execSQL("UPDATE " + table + " SET " + KEY_MODE + " = 1;");
Since it's so simple, you can do this right in your onUpgrade()
method.
You could have done that much easier:
1.) During onUpgrade(): "add column newcolumn default 1". This would add a new column with all newcolumns containing 1.
2.) onUpgrade() is already run: update table set newcolumn=1: Without a WHERE clause the whole table is affected.
There's not need to walk thru all rows.
What you want to do requires an SELECT...FOR UPDATE OF/UPDATE...WHERE CURRENT OF. I didn't do that with SQLite, so I don't know if this is supported.
In your situation (onUpgrade is already run) use 2.)
精彩评论