Android SQLite Upgrade without losing data

Question

I have created a SQLite database successfully and it works fine. However when the onUpgrade method is called, I'd like to do so without losing data. The app I'm developing is a quiz app. Simply, when the onCreate method is called I create and prepopulate a database with questions, answers etc. The last column is whether they have set the question as a favourite or not. What I would like to do is that when the onUpgrade method is called, I'd like to temporarily save that one column, drop the whole database, recreate it with any edits I've made to old questions and add any new questions then re-add back the questions that they set as favourites.

So one option I tried was the following:

db.execSQL("ALTER TABLE quiz RENAME TO temp_quiz");
onCreate(db);
db.execSQL("INSERT INTO quiz (favouries) SELECT favourites FROM temp_quiz");
db.execSQL("DROP TABLE IF EXISTS temp_quiz");

However this doesn't work owing to the fact INSERT INTO just adds new rows rather than replacing the existing rows. I have also tried REPLACE INTO, INSERT OR REPLACE INTO and

db.execSQL("INSERT INTO quiz (_id, favouries) SELECT _id, favourites FROM temp_quiz");

of which none work.

Currently I do have it set up to work by altering the name of the table, calling the onCreate(db) method and then setting up a cursor which reads each row and uses the db.update() method as shown below:

int place = 1;
int TOTAL_NUMBER_OF_ROWS = 500;

while (place < TOTAL_NUMBER_OF_ROWS) {

String[] columns = new String[] { "_id", ..........., "FAVOURITES" };
// not included all the middle columns

Cursor c = db.query("temp_quiz", columns, "_id=" + place, null, null, null, null);

c.moveToFirst(); 

String s = c.getString(10);
// gets the value from the FAVOURITES column


ContentValues values = new ContentValues(); 
values.put(KEY_FLAG, s);

String where = KEY_ROWID + "=" + place;

db.update(DATABASE_TABLE, values, where, null);

place++;

c.close();

}

However whilst this works it is extremely slow and will only get worse as my number of questions increases. Is there a quick way to do all this?

Thank you! P.S. Ideally it should only update the row if the row is present. So if in an upgrade I decide to remove a question, it should take this into account and not add a new row if the row doesn't contain any other data. It might be easier to get it to remove rows that don't have question data rather than prevent them being added.

Answer 1

changed it to:

db.execSQL("UPDATE new_quiz SET favourites = ( SELECT old_quiz.favourites 
FROM old_quiz WHERE new_quiz._id = old_quiz._id) WHERE EXISTS 
( SELECT old_quiz.favourites FROM old_quiz WHERE new_quiz._id = old_quiz._id)");

Which works :D