CODEWITHSUNDEEP
javaandroidsqlite
Habib Mhamadi
Habib Mhamadi

Reputation: 779

Can't delete SQLite column with (Begin Transaction) method

In the Android Studio SQLite database, I have a table "title" With two columns (name, id) I want to delete the id column, I used the Transaction Method but it doesn't work. After copying the name column to a new table, I want that the new table should have itself id column.

     try {
            SQLiteDatabase sqLiteDatabase = this.openOrCreateDatabase("db", MODE_PRIVATE, null);

    sqLiteDatabase.execSQL("BEGIN TRANSACTION");
            sqLiteDatabase.execSQL("CREATE TEMPORARY TABLE t1(name VARCHAR)");
            sqLiteDatabase.execSQL("INSERT INTO t1 SELECT name FROM title");
            sqLiteDatabase.execSQL("DROP TABLE title");
            sqLiteDatabase.execSQL("CREATE TABLE title (name VARCHAR, id INTEGER PRIMARY KEY)");
            sqLiteDatabase.execSQL("INSERT INTO title SELECT name FROM t1");
            sqLiteDatabase.execSQL("DROP TABLE t1");
    sqLiteDatabase.execSQL("COMMIT");

           } catch (Exception e) {

        }

And I also tried this

 try {
            SQLiteDatabase sqLiteDatabase = this.openOrCreateDatabase("db", MODE_PRIVATE, null);

    sqLiteDatabase.execSQL("BEGIN TRANSACTION");
            sqLiteDatabase.execSQL("CREATE TEMPORARY TABLE t1(name VARCHAR,id INTEGER PRIMARY KEY)");
            sqLiteDatabase.execSQL("INSERT INTO t1 SELECT name FROM title");
            sqLiteDatabase.execSQL("DROP TABLE title");
            sqLiteDatabase.execSQL("CREATE TABLE title (name VARCHAR, id INTEGER PRIMARY KEY)");
            sqLiteDatabase.execSQL("INSERT INTO title SELECT name,id FROM t1");
            sqLiteDatabase.execSQL("DROP TABLE t1");
    sqLiteDatabase.execSQL("COMMIT");

           } catch (Exception e) {

        }

Upvotes: 1

Views: 165

Answers (1)

MikeT
MikeT

Reputation: 57043

Your issue is due to the trapped exception :-

SQLiteException: table title has 2 columns but 1 values were supplied (code 1 SQLITE_ERROR): , while compiling: INSERT INTO title SELECT name FROM t1

i.e you are not providing a value for the id column.

Change to use

sqLiteDatabase.execSQL("INSERT INTO title SELECT name, null FROM t1");

or 

sqLiteDatabase.execSQL("INSERT INTO title (name) SELECT name FROM t1");

Upvotes: 1

Related Questions