Add New Column

Once table is created a new column can be added later, it will created at the end of column list. Columns cannot be dropped however column can be removed by creating another table without dropped column and copying the data from older table.

Syntax : ALTER TABLE TABLE_NAME ADD COLUMN COLUMN_NAME DATA_TYPE;

DROP TABLE IF EXISTS Users;
CREATE TABLE IF NOT EXISTS Users (
Username Text Primary Key
, FirstName Text
, LastName Text
);
 

INSERT INTO Users(Username, FirstName, LastName) values ('johnd','John','Dekota');
INSERT INTO Users(Username, FirstName, LastName) values ('maryd','Mary','Denware');
INSERT INTO Users(Username, FirstName, LastName) values ('scotth','Scott','Hamilton');

SELECT * FROM Users;

The below code adds a new column called EmailAddress with Text as datatype in users table, the data will be empty in that column.

ALTER TABLE Users ADD COLUMN EmailAddress Text;

SELECT * FROM Users;

Rename columns

Following example renames firstname and lastname columns to PersonFirstName and PersonLastName.

DROP TABLE IF EXISTS Users;
CREATE TABLE IF NOT EXISTS Users (
Username Text Primary Key
, FirstName Text
, LastName Text
);
 
/* Insert some example data */
INSERT INTO Users(Username, FirstName, LastName) values ('johnd','John','Dekota');
INSERT INTO Users(Username, FirstName, LastName) values ('maryd','Mary','Denware');
INSERT INTO Users(Username, FirstName, LastName) values ('scotth','Scott','Hamilton');

/* Rename the existing table */
ALTER TABLE Users RENAME TO Users_old;

/* Create the new table with correct column names */
DROP TABLE IF EXISTS Users;
CREATE TABLE IF NOT EXISTS Users (
Username Text Primary Key
, PersonFirstName Text
, PersonLastName Text
);

/* Insert the rows in new table. */
Insert Into Users (Username, PersonFirstName, PersonLastName) SELECT * FROM Users_old;

/* Drop the old table after rows are copied to new table */
DROP TABLE Users_old;

/* check if we got all the rows */
SELECT * FROM Users;

If there are any indexes, triggers or views on older table then they will also need to be recreated. You can get the actual sql which was used to create objects from SYS_OBJECTS table in sqltext column.