INSERT INTO table SELECT values FROM other_table not working

Question

I'm trying to drop a table from my database, so I'm following the official instructions for doing that. This is what my code looks like:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // A list of the values I want to preserve
    String contents = Values.KEY_ROWID + ","
            + Values.KEY_NAME + ","
            + Values.COURSE_KEY_TEACHER + ","
            + Values.KEY_DESCRIPTION + ","
            + Values.KEY_ROOM + ","
            + Values.COURSE_KEY_TIMES_OF_DAY + ","
            + Values.COURSE_KEY_COLOR;

    db.execSQL("CREATE TABLE course_backup("
        + Values.KEY_ROWID + " int, "
        + Values.KEY_NAME + " text not null, "
        + Values.COURSE_KEY_TEACHER + " text not null, "
        + Values.KEY_DESCRIPTION + " text, "
        + Values.KEY_ROOM + " int, "
        + Values.COURSE_KEY_TIMES_OF_DAY + " text, "
        + Values.COURSE_KEY_COLOR + " int not null);");
    db.execSQL("INSERT INTO course_backup SELECT "
        + contents
        + " FROM course_backup;");
    /*db.execSQL("DROP TABLE " + Values.COURSE_TABLE + ";");
    db.execSQL(Values.COURSE_DATABASE_CREATE);
    db.execSQL("INSERT INTO " + Values.COURSE_TABLE + " SELECT "
    + contents + " FROM course_backup");*/
}

But when I pull a copy of the database to my computer and look at the course_backup table, it's completely empty. All the columns were created right, but there isn't a single row of data. What am I doing wrong?

score 1 · Accepted Answer · answered Dec 28 '12 at 00:34

1

You're selecting from course_backup, which is the table you just created. Quite reasonably (given that there are no inserts into course_backup in between) this table is empty.

answered Dec 28 '12 at 00:34

BrianC

316
1
6

Hahaha sometimes it really does take a second pair of eyes to spot the obvious. ;) Thanks! – mightimaus Dec 28 '12 at 00:50

score 1 · Answer 2 · answered Dec 28 '12 at 00:35

1

You're selecting the rows from your empty backup table.

answered Dec 28 '12 at 00:35

LWChris

3,320
1
22
39

score 0 · Answer 3 · edited May 23 '17 at 12:04

0

You're missing the columns that you'd like to INSERT into. See this related discussion: Insert into ... values ( SELECT ... FROM ... ). Oh yes, and as the others pointed out, your source and destination are the same empty table.

edited May 23 '17 at 12:04

Community

1
1

answered Dec 28 '12 at 00:34

ryanbwork

2,123
12
12

INSERT INTO table SELECT values FROM other_table not working

3 Answers3