ALTER TABLE – Add, Remove, or Modify Column in SQL

We can use the ALTER TABLE command to either add or remove columns in SQL. The ALTER command is considered part of a DDL statement since it modifies the structure of a table. We can also use ALTER to modify an existing column if a change is needed. Similarly we can also use alter to rename columns.

Adding Single or Multiple Columns

We can add either single or multiple columns using the ALTER TABLE ADD column syntax.

Adding a single column:

ALTER TABLE <schema_name>.<table_name>
ADD <column_name> datatype; — Alter Table Add Column Syntax

Example:

Adding multiple columns:

ALTER TABLE <schema_name>.<table_name>
ADD <column_name> datatype,
<column_name_2> datatype,
<column_name_3> datatype; — Add Multiple Columns

Example:

modify table and add multiple columns using sql

Dropping, Modifying, and Renaming Columns

Instead of adding we can also remove unwanted columns from our tables.

Removing a column:

ALTER TABLE <schema_name>.<table_name>
DROP COLUMN <column_name>; — Alter Table Drop Column Syntax

Example:

Note: SQLite does not support the dropping of columns. It’s also typical to not have privileges to drop columns. In that case we’d need permissions from a database admin.

We can also use a variation of the command if we need to modify a datatype or setting such as NOT NULL to a column.

Modify an existing column:

ALTER TABLE <schema_name>.<table_name>
ALTER COLUMN <column_name> datatype; — SQL Server

ALTER TABLE <schema_name>.<table_name>
MODIFY <column_name> datatype; — MySQL, Oracle

ALTER TABLE <schema_name>.<table_name>
ALTER COLUMN <column_name> TYPE datatype; — PostgreSQL

Renaming columns:

The ALTER command only supports renaming columns for certain databases. For instance, if we use SQL Server with SSMS it will only support its own built in function.

EXEC sp_rename ‘<schema_name>.<table_name>.<column_name>‘, ‘<new_column_name>’, ‘COLUMN’; — SQL Server

ALTER TABLE <schema_name>.<table_name>
RENAME column_name TO new_column_name; — MySQL, Oracle, PostgreSQL, SQLite, and others

Whether we are adding, removing, or modifying columns in a table – the ALTER TABLE command has you covered. Depending on the database you’re using will depend on how far its application goes. We pointed out that some functions of the ALTER statement don’t work on specific databases. Some also use their own keywords so just keep that in mind.

Adding Single or Multiple Columns

Dropping, Modifying, and Renaming Columns

Related