How To Use Alter Table Statement In SQL?

Have you ever tried to add, delete or modify columns in a table? If yes, then, ALTER TABLE is the command which you have to use. So, in this article on Alter Table, I will discuss how you can use this command to modify the columns in a table. 

The following topics will be covered in this article:

• What is the Alter Table statement?
• Operations of Alter Table:
  1. ALTER TABLE ADD Column
  2. ALTER TABLE DROP Column
  3. ALTER TABLE MODIFY Column

What is the Alter Table statement?

This statement is used to add, modify, or delete column(s) in an existing table. Also, this statement can be used to add/drop constraints on an existing table. The ALTER TABLE statement can be used in SQL along with the following:

• ADD Column
• DROP Column
• MODIFY Column

Let’s discuss each one of these ones by one by considering the following table:

If you want to know, how to create a table in SQL, you can refer to my article on CREATE TABLE.

Operations:

ALTER TABLE ADD Column

This statement is used to add a column or add multiple columns in an existing table.

Syntax:

#Add Single Column
ALTER TABLE TableName
ADD ColumnName datatype;
#Add Multiple Columns
ALTER TABLE TableName 
ADD ColumnName datatype,
ADD ColumnName datatype,
ADD ColumnName datatype
;

Example:

ALTER TABLE students
ADD dob date;

You will see an output, that the column (dob) is added into the table as below:

You can go forward, and insert data into the column, by using the insert query in SQL.

ALTER TABLE DROP Column

This statement is used to drop a column or multiple columns in an existing table.

Syntax:

ALTER TABLE TableName
DROP ColumnName datatype;

Example:

ALTER TABLE students
DROP dob date;

You will see an output, that the column is deleted from the table as below:

ALTER TABLE MODIFY Column

This statement is used to modify the data type of a column in an existing table.

Syntax:

#SQL Server 
ALTER TABLE TableName
ALTER COLUMN ColumnName datatype;
#MySQL
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Example:

Let’s add back the dob column, and change the data type of that column to year;

To add back the column mention the following query:

ALTER TABLE Persons
ALTER COLUMN dob year;

Now, to change the data type of the column, mention the code below:

ALTER TABLE Persons
ALTER COLUMN dob year;

You will see an output, that the dob column is added back to the table and has the data type ‘year’. Refer below.

With this, we come to an end to this article. I hope you understood, how to use the above commands. If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of this article and I will get back to you.