Sqlite Alter Table Add Multiple Columns in a Single Statement

sqlite alter table add MULTIPLE columns in a single statement

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.

Alter table by add multiple column in a single statement ormlite

So Finally after a lot search i don't think that there is any way to put multiple column in a single statement in OrmLite as SQLite does not support multiple iteration for ADD keyword.

Thanks for the answer SQLite alter table

and even it's mentioned in the doc SQLite docs

SQLITE : How to add several columns to a table?

Use repeated calls to ALTER TABLE.

You should not have to do this too often anyway.

Adding multiple columns to a table in sqllite

No,you can't add multiple columns in single query execution. SQLite supports a limited subset of ALTER TABLE.therefore you have to add them one by one.

see documentation at sqlite

How to add two new Column to Android SQLite Database?

You can only add one column at a time. Split it into two ALTER TABLE statements and you should be fine.

private static final String ALTER_USER_TABLE_ADD_USER_SOCIETY = 
    "ALTER TABLE user_table ADD user_society TEXT";
private static final String ALTER_USER_TABLE_ADD_USER_STREET1 = 
    "ALTER TABLE user_table ADD user_street1 TEXT";

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
{
   db.execSQL(ALTER_USER_TABLE_ADD_USER_SOCIETY);
   db.execSQL(ALTER_USER_TABLE_ADD_USER_STREET1);
}

SQLite add multiple columns using for loop

Only your first mention of the ALTER TABLE command is correct: you must not use parentheses.

And it is not possible to use parameters for table/column names. You have to put the column name directly into the SQL command string (and quote it if it is not a valid identifier):

for k in matFile:
    c.execute("ALTER TABLE table_name ADD " + k)

And instead of executing one command for each column, you could just construct the CREATE TABLE command with all columns:

sql = "CREATE TABLE tab (ID INTEGER PRIMARY KEY," + ",".join(matFile) + ")"

And the default limit on the number of columns is 2000, so you will not be able to do this anyway. You should properly normalize your database structure.

How to update multiple columns with a single statement with multiple conditions

You should run 2 separate update queries. The problem is that Country can be null independently of Genre. So however you combine the conditions Country IS NOT NULL and Genre IS NOT NULL in the where clause of the query, you will miss some columns that could have been updated, or you will update some columns with null values.

Now, I haven't seen the implementation of returnCommaDelimitedValue. It is possible that it returns null when the string argument is null. In that case, you might consider remove the where clause completely and update the countryN and genreN columns in the same query. In that case, if Country is null, this will also make Country1 null, so this might be something that you want. If almost all rows have a non-null Country and Genre, this approach might be faster.

SQLite - adding columns and updating them using another table

The ALTER TABLE statement does not support multiple columns, so you must add them in 2 separate statements:

ALTER TABLE Species ADD Name1 TEXT;
ALTER TABLE Species ADD ScientificName1 TEXT;

Your UPDATE statement is correct if your version of SQLite is 3.33.0+.

For previous versions (3.15.0+) you could use ROW VALUES:

UPDATE Species
SET (Name1, ScientificName1) = (
  SELECT temp.Name, temp.ScientificName
  FROM temp
  WHERE temp.Id = Species.Id
);

See the demo.

---

Related Topics