Is it possible to alter table add MULTIPLE columns in a single statement in sqlite? The following would not work.
alter table test add column mycolumn1 text, add column mycolumn2 text;
Add a comment
|
3 Answers
No, you have to add them one at a time. See the syntax diagram at the top of SQLite's ALTER TABLE documentation:
There's no loop in the ADD branch so no repetition is allowed.
1 Comment
mu is too short
@JoshPinter: Thanks but I think the syntax diagrams in the SQLite docs deserve a lot of the credit, the syntax diagrams are great, everyone should use them in their docs.
The only thing so far possible that I use is
BEGIN TRANSACTION;
ALTER TABLE tblName ADD ColumnNameA TEXT DEFAULT '';
ALTER TABLE tblName ADD ColumnNameB TEXT DEFAULT '';
ALTER TABLE tblName ADD ColumnNameC TEXT DEFAULT '';
COMMIT
Note that there are ; on purpose to make the query be read as multiple lines.
Then I run this query and get multiple columns added in on run... So no not in one line, but yes in one query its possible.
1 Comment
Nicolas
Note that the
migrate method is already called in a transaction so there's limited benefit to this approach.The answer from '@mu is too short' is right. Providing an optimized solution for adding multiple columns using the transactions feature in SQL.
String alterTableQuery = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN ";
List<String> newColumns = ..// Your new columns
db.beginTransaction();
for (String column : newColumns){
db.execSQL(alterTableQuery + column + " VARCHAR");
}
db.setTransactionSuccessful();
db.endTransaction();
I hope this will help someone.
