Slide 6.12: SQL ALTER TABLE statement

Slide 6.11: SQL UPDATE statement
Slide 6.13: SQL ALTER TABLE statement (cont.)
Home Print version

SQL ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table. The where clause specifies which record or records that should be updated.

ALTER TABLE – ADD Column

To add a column in a table, use the syntax:

 ALTER TABLE table_name
   ADD column_name datatype;

The SQL adds an Email column to the Customers table:

 ALTER TABLE Customers
   ADD Email VARCHAR(255);

ALTER TABLE – DROP COLUMN

To delete a column in a table, use the syntax:

 ALTER TABLE table_name
   DROP COLUMN column_name;

The SQL deletes an Email column from the Customers table:

 ALTER TABLE Customers
   DROP COLUMN  Email;

ALTER TABLE – ALTER/MODIFY COLUMN

To change the data type of a column in a table, use the syntax:

 ALTER TABLE table_name
   MODIFY column_name datatype;

Demonstration
Below is an SQL test area from W3Schools, which uses the well-known Northwind sample database. The tables here are for read only because of the problem of embedding the scripts. For a fully working example, check this by using Chrome.

Result:

The Database includes:

Tablename	Record
Customers	91
Categories	8
Employees	10
OrderDetails	518
Orders	196
Products	77
Shippers	3
Suppliers	29

W3C WebSQL

WebSQL Browser Support