SQL ALTER TABLE |
SQL Sample Code - SQL ALTER TABLE statement. |
The statements below are used to change a table. We can either add (ADD), delete (DROP) or change (ALTER), (MODIFY) columns in a database table.
ADD:
| ALTER TABLE table_name ADD column_name datatype; |
DROP:
| ALTER TABLE table_name DROP column_name datatype; |
ALTER:
Oracle and MySQL:
| ALTER TABLE table_name MODIFY column_name datatype; |
SQL Server:
| ALTER TABLE table_name ALTER column_name datatype; |
See the SQL Samples section below for an explanation.
What does the SQL ALTER TABLE statement do? |
The SQL ALTER TABLE statement modifies the columns within a specified database table.
We can either add (ADD), delete (DROP) or change (ALTER)or (MODIFY) columns in a database table.
SQL Sample 1 – SQL ALTER TABLE (DROP COLUMN) |
In this SQL sample we will use the database table ‘tblCompany’.
| Company_ID | CompanyName | Address | Town | Sales |
| 1 | SQL Sample | 1 Sample St | Hamburg | 10000 |
| 2 | SQL Code Land | 2 Code Rd | Hamburg | 20000 |
| 3 | Sample Code World | 66 SQL St | Curry | 18000 |
| 4 | SQL Reference Ltd | 34 Reference St | Pisa | 17000 |
In this SQL sample we want to remove (DROP) the Sales column from the tblCompany table.
Use the ALTER TABLE statement below:
| ALTER TABLE tblCompany DROP COLUMN Sales; |
The table of results will look like this:
| Company_ID | CompanyName | Address | Town |
| 1 | SQL Sample | 1 Sample St | Hamburg |
| 2 | SQL Code Land | 2 Code Rd | Hamburg |
| 3 | Sample Code World | 66 SQL St | Curry |
| 4 | SQL Reference Ltd | 34 Reference St | Pisa |
SQL Sample 2 – SQL ALTER TABLE (DROP COLUMN) |
In this SQL sample we will use the database table ‘tblCompany’.
| Company_ID | CompanyName | Address | Town |
| 1 | SQL Sample | 1 Sample St | Hamburg |
| 2 | SQL Code Land | 2 Code Rd | Hamburg |
| 3 | Sample Code World | 66 SQL St | Curry |
| 4 | SQL Reference Ltd | 34 Reference St | Pisa |
In this SQL sample we want to ADD the Sales column from the tblCompany table.
Use the ALTER TABLE statement below:
| ALTER TABLE tblCompany ADD COLUMN Sales int; |
The ‘int’ data type has been used in this example. Go to the data types section to learn more.
The table of results will look like this:
| Company_ID | CompanyName | Address | Town | Sales |
| 1 | SQL Sample | 1 Sample St | Hamburg | |
| 2 | SQL Code Land | 2 Code Rd | Hamburg | |
| 3 | Sample Code World | 66 SQL St | Curry | |
| 4 | SQL Reference Ltd | 34 Reference St | Pisa |
SQL Sample 3 – SQL ALTER TABLE (MODIFY)(ALTER) |
In this SQL sample we will use the database table ‘tblCompany’.
| Column | Data Type |
| Company_ID | int |
| CompanyName | varchar(100) |
| Address | varchar(255) |
| Town | varchar(20) |
| Sales | int |
In this SQL sample we want to change the data type of the Town column in the tblCompany table from a maximum of 20 Characters to a maximum of 50 characters.
Use the ALTER TABLE statement below:
Oracle and MySQL:
| ALTER TABLE tblCompany MODIFY COLUMN Town varchar(50); |
SQL Server:
| ALTER TABLE tblCompany ALTER COLUMN Town varchar(50); |
The table of results will look like this:
| Column | Data Type |
| Company_ID | int |
| CompanyName | varchar(100) |
| Address | varchar(255) |
| Town | varchar(20) |
| Sales | int |
Please note: Be careful when changing the data types and data type lengths of columns. Reducing the length of a column may lose the existing data in that particular database column.
Related SQL Sample Code:
|
|