Alter Table Statement in MySQL : How to Modify Multiple Columns in Table

In this article I will show you how to modify multiple columns in table.

MODIFY MULTIPLE COLUMNS IN TABLE

Syntax

Follow the below syntax to modify multiple columns in a table in MySQL using the Alter Table statement.

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

Where;

• table_name : Name of the table to modify.
• new_column_name : Name of the new column to modify in the table.
• column_definition : Data type and definition of the column such as NULL or NOT NULL.
• FIRST | AFTER column_name : This is optional, it tells where in the table to create the column. If this is not mentioned by default new column will be added to the end of the table.

Example

Let’s see how to add multiple columns in MySQL table using the Alter Table statement.

ALTER TABLE contacts
  MODIFY last_name varchar(55) NULL
    AFTER contact_type,
  MODIFY first_name varchar(30) NOT NULL;

This ALTER TABLE example will modify two columns to the contacts table – last_name and first_name.

The last_name field will be changed to a varchar(55) NULL column and will appear after the contact_type column in the table. The first_name column will be modified to a varchar(30) NOT NULL column (and will not change position in the contacts table definition, as there is no FIRST | AFTER specified).