ALTER TABLE in MySQL | Modifying a Column in MySQL

ALTER TABLE

The statement ALTER TABLE can be used in MySQL to add a column, modify a column, drop a column, rename a column from a table.

Suppose there is a table named students as shown below:

1. Add a column to a table using ALTER TABLE with ADD

The syntax for adding a column to a table is shown below:

Syntax:

ALTER TABLE table_name
ADD 
new_column_name column_definition
[FIRST|AFTER column_name]

In the above syntax:

• ADD is used with ALTER TABLE to add a column in a table.
• The name of the new column to be added (new_column_name) is written after the ADD clause, and then the column definition which contains the data type(mandatory) and other optional details about the column is specified.
• After that, the position of the column is specified. FIRST is used to add the column as the first column of the table, and column name (column_name) with AFTER clause is used to add the column after the specified column.
• The position of the column is optional and if not specified the new column gets added as the last column.

For example - Let's add a column ‘semester’ to the table student after the ‘name’ column.

ALTER TABLE students
ADD 
semester INT
AFTER name;

After the execution of the above query, the table will look like this:

It can be seen from the above-shown output, a new column named semester is added after the column ‘name’ which has the default value NULL for each row.

2. Add Multiple columns to a table 

Multiple columns can be added to a table by using multiple ADD statements in syntax for adding a single column to a table. For example - Let's add multiple columns to the original “student” table.

ALTER TABLE students
ADD semester INT
AFTER name;
ADD cgpa FLOAT

After the execution of the above query the table will look like shown below:

It can be seen in the above table two columns are added “semester” and “cgpa” which is the result of the two ADD statements in the query.

3. Modify a column using ALTER TABLE with MODIFY

We can modify one or multiple columns of a table using MODIFY with ALTER TABLE statement.

The syntax for this is shown below:

ALTER TABLE table_name
MODIFY 
column_name column_definition
[FIRST|AFTER column_name]   

The above syntax is similar to the syntax for adding a column to a table. The only difference is we use MODIFY in place of ADD. For example - Suppose there is a column named “name” in the “customers” table whose data type is varchar and defined length is 255.

Let's modify its specified length to 100.

ALTER TABLE customers
MODIFY 
name varchar(100); 

The above-written query will modify the specified length of the name to 100 from 255.

Similarly, we can modify multiple columns by using multiple MODIFY statements.

4. Rename columns using ALTER TABLE with CHANGE COLUMN

We can rename a column of a table using the CHANGE COLUMN keyword with ALTER TABLE.

The syntax for this is shown below:

ALTER TABLE table_name
CHANGE COLUMN original_column_name new_column_name column_definition
[FIRST | AFTER column_name];

In the above syntax:

• CHANGE COLUMN keyword is used with ALTER TABLE to rename a column of a table.
• The original name is written after the CHANGE COLUMN keyword followed by the new name of column and column definition.
• The third line is used to specify the new position of the column.

For example - Suppose there is a column named “name” in a table named “customers”.

Let's change the name of the column.

ALTER TABLE customers
CHANGE COLUMN name customer_name VARCHAR(100)

The above-written query will change the “name” column's name to “customer_name”.

5. Drop a column using ALTER TABLE with DROP COLUMN

We can drop a column or multiple columns using DROP COLUMN with ALTER TABLE.

The syntax for this is shown below:

ALTER TABLE table_name
DROP COLUMN column_name;

In the above syntax, the DROP COLUMN keyword is used followed by the name of the column to be dropped. For example - 

Let's drop the column “stream” from the original “students” table. 

ALTER TABLE students
DROP COLUMN stream;

After the execution of the above query the table will look like shown below:

It can be seen in the above table that the “stream” column is not present now as it has been dropped.

Similarly, multiple columns can be dropped by using multiple DROP statements.