ChatGPT解决这个技术问题 Extra ChatGPT

sqlite alter table add MULTIPLE columns in a single statement

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;

C
Community

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

https://www.sqlite.org/images/syntax/alter-table-stmt.gif

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


@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.
M
Michiel Krol

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.


Note that the migrate method is already called in a transaction so there's limited benefit to this approach.
A
Arun P M

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.


a
anand arun

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

use the above redifined query


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.