183

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,438
  • 68
  • 55
  • 126
user775187
  • 20,311
  • 8
  • 27
  • 35

4 Answers4

347

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
  • 413,090
  • 67
  • 810
  • 771
  • 14
    @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
6

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
  • 113
  • 2
  • 8
  • 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
1

The answer from @mu is too short' is right. As an extra, adding an optimized workaround for adding multiple columns using the benefit of transactions 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
  • 302
  • 1
  • 11
-18

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

use the above redifined query

anand arun
  • 7
  • 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