MySQL ALTER TABLE Statement

MySQL ALTER statement is used when you want to change the name of your table or any table field. It is also used to add or delete an existing column in a table.

The ALTER statement is always used with “ADD”, “DROP” and “MODIFY” commands according to the situation.

1) ADD a column in the table

Syntax:

ALTER TABLE table_name
ADD column_name datatype;

The following SQL adds an “Email” column to the “Customers” table:

Example

ALTER TABLE Customers
ADD Email varchar(255);

2) Add multiple columns in the table

Syntax:

 ALTER TABLE table_name  
 ADD new_column_name column_definition  
 [ FIRST | AFTER column_name ],  
ADD new_column_name column_definition  
[ FIRST | AFTER column_name ],  
  ...  
;  

Example:

In this example, we add two new columns “Contact”, and City in the existing table “Customers”. cus_contact is added after cus_surname column and cus_salary is added after cus_City column.

ALTER TABLE Customer
ADD cus_contact varchar(100) NOT NULL  
AFTER cus_surname,  
ADD cus_salary int(100) NOT NULL  
AFTER cus_city; 

3) ALTER TABLE — DROP COLUMN

To delete a column in a table, use the following syntax (notice that some database systems don’t allow deleting a column):

ALTER TABLE table_name
DROP COLUMN column_name;

The following SQL deletes the “Email” column from the “Customers” table:

Example

ALTER TABLE Customers
DROP COLUMN Email;

4) ALTER TABLE — MODIFY COLUMN

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

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

MySQL ALTER TABLE Example

Look at the “Customers” table:

Customers

Now we want to add a column named “ContactNumber” in the “Customers” table.

We use the following SQL statement:

Example

ALTER TABLE Customers
ADD ContactNumber int(10);

Notice that the new column, “ContactNumber”, is of type int and is going to hold a number. The data type specifies what type of data the column can hold.

5) Change Data Type Example

Now we want to change the data type of the column named “PostalCode” in the “Customers” table.

We use the following SQL statement:

Example

ALTER TABLE Customers
MODIFY COLUMN PostalCode int(10);

6) DROP COLUMN Example

Next, we want to delete the column named “ContactNumber” in the “Customers” table.

We use the following SQL statement:

Example

ALTER TABLE Customers
DROP COLUMN ContactNumber;