HTML 
CSS 
PHP 
JavaScript 
SQL 
Bootstrap 
Solidity 
jQuery 
Git 
Chrome DevTools 
C++ 
Python TL;DR – The ALTER TABLE statement in SQL is used to delete, add, or modify table columns. You can also use it to drop and add various table constraints.
Contents
- 1. Adding a new column to the table
- 2. Deleting a column
- 3. Changing the data type of a particular column
- 3.1. In MySQL / Oracle (< 10G)
- 3.2. In SQL Server / MS Access
- 3.3. In Oracle 10G and later
- 4. Examples using a demo database
- 4.1. The Developers table
- 4.2. Adding a column called BirthDate
- 4.3. Changing the data type to year
- 4.4. Deleting the BirthDate column
Adding a new column to the table
Example
ALTER TABLE mytable_name
ADD mycolumn_name datatype;Note: the new column will be added to the end of the table by default. You can add multiple columns by separating them with commas.
Deleting a column
Example
ALTER TABLE mytable_name
DROP COLUMN mycolumn_name;Changing the data type of a particular column
Note: when changing the data type, make sure the old one and the new one are compatible. Otherwise, you might get conversion errors.
In MySQL / Oracle (< 10G)
Example
ALTER TABLE mytable_name
MODIFY COLUMN mycolumn_name datatype;In SQL Server / MS Access
Example
ALTER TABLE mytable_name
ALTER COLUMN mycolumn_name datatype;
Pros Main Features
- Easy to use with a learn-by-doing approach
- Offers quality content
- Gamified in-browser coding experience
- The price matches the quality
- Suitable for learners ranging from beginner to advanced
- Free certificates of completion
- Focused on data science skills
- Flexible learning timetable
Pros Main Features
- Simplistic design (no unnecessary information)
- High-quality courses (even the free ones)
- Variety of features
- Nanodegree programs
- Suitable for enterprises
- Paid Certificates of completion
Pros Main Features
- A wide range of learning programs
- University-level courses
- Easy to navigate
- Verified certificates
- Free learning track available
- University-level courses
- Suitable for enterprises
- Verified certificates of completion
In Oracle 10G and later
Example
ALTER TABLE mytable_name
MODIFY mycolumn_name datatype;Examples using a demo database
The Developers table
| ID | Name | City | Country |
|---|---|---|---|
| 1 | Tom Kurkutis | New York | USA |
| 2 | Ana Fernandez | London | UK |
| 3 | Antonio Indigo | Paris | France |
| 4 | Aarav Kaelin | Delhi | India |
| 5 | Andrew Tumota | Miami | USA |
| 6 | Basma Zlata | Miami | USA |
Adding a column called BirthDate
Example
ALTER TABLE Developers
ADD BirthDate date;The result
| ID | Name | City | Country | BirthDate |
|---|---|---|---|---|
| 1 | Tom Kurkutis | New York | USA | |
| 2 | Ana Fernandez | London | UK | |
| 3 | Antonio Indigo | Paris | France | |
| 4 | Aarav Kaelin | Delhi | India | |
| 5 | Andrew Tumota | Miami | USA | |
| 6 | Basma Zlata | Miami | USA |
Changing the data type to year
Example
ALTER TABLE Developers
ALTER COLUMN BirthDate year;Deleting the BirthDate column
Example
ALTER TABLE Developers
DROP COLUMN BirthDate;The result
| ID | Name | City | Country |
|---|---|---|---|
| 1 | Tom Kurkutis | New York | USA |
| 2 | Ana Fernandez | London | UK |
| 3 | Antonio Indigo | Paris | France |
| 4 | Aarav Kaelin | Delhi | India |
| 5 | Andrew Tumota | Miami | USA |
| 6 | Basma Zlata | Miami | USA |
