AWS Global Infrastructure

Databases

Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (15 Blogs)
  • MySQL (30 Blogs)
  • SQL Essentials Training and Certification (4 Blogs)
SEE MORE

How To Use Alter Table Statement In SQL?

Last updated on Mar 09,2023 2.9K Views

19 / 37 Blog from Introduction to 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. 

SQL-Alter Table -EdurekaThe following topics will be covered in this article:

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:

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.

studentID

firstname

lastname

phonenumber

1

Rohan

Rathore

9876543210

2

Sonali

Saxena

9876567864

3

Ajay

Agarwal

9966448811

4

Geeta

Gulati

9765432786

5

Shubham

Sinha

9944888756

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:

studentIDfirstnamelastnamephonenumberdob

1

Rohan

Rathore

9876543210

2

Sonali

Saxena

9876567864

3

Ajay

Agarwal

9966448811

4

Geeta

Gulati

9765432786

5

Shubham

Sinha

9944888756

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 can go forward and learn more about SQL Server and its framework from the Microsoft SQL Certification.

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

studentID

firstname

lastname

phonenumber

1

Rohan

Rathore

9876543210

2

Sonali

Saxena

9876567864

3

Ajay

Agarwal

9966448811

4

Geeta

Gulati

9765432786

5

Shubham

Sinha

9944888756

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.

studentIDfirstnamelastnamephonenumberdob

1

Rohan

Rathore

9876543210

2

Sonali

Saxena

9876567864

3

Ajay

Agarwal

9966448811

4

Geeta

Gulati

9765432786

5

Shubham

Sinha

9944888756

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.

Comments
0 Comments

Join the discussion

Browse Categories

webinar REGISTER FOR FREE WEBINAR
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.

image not found!
image not found!

How To Use Alter Table Statement In SQL?

edureka.co