195

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;
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user775187
  • 22,311
  • 8
  • 28
  • 36

4 Answers4

365

No, you have to add them one at a time. See the syntax diagram at the top of SQLite's ALTER TABLE documentation:

ALTER TABLE syntax

There's no loop in the ADD branch so no repetition is allowed.

Community
  • 1
  • 1
mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • 16
    @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. – mu is too short May 03 '14 at 00:48
18

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.

Michiel Krol
  • 243
  • 3
  • 9
  • Note that the `migrate` method is already called in a transaction so there's limited benefit to this approach. – Nicolas Aug 09 '20 at 21:27
3

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.

Arun P M
  • 352
  • 1
  • 15
-19

alter table test add column mycolumn1 text; alter table test add column mycolumn2 text;

use the above redifined query

anand arun
  • 5
  • 5
  • 6
    That's not a single statement - anything with `;` as a separator must be executed as multiple statements, and it often does matter. The other answer already demonstrated that what the OP is asking for is impossible, however. – o11c Oct 08 '14 at 08:45