How to :- Alter Table or Add Multiple Columns in Table? – MySQL

Guys In this article we will learn how we can Alter Table or Add Multiple columns in Table in MySQL. To add add a column in a table we normally use mysql add column statement.

Add Multiple Columns in Table

Syntax

Follow the below syntax to add multiple columns in table.

ALTER TABLE table_name
  ADD new_column_name column_definition
    [ FIRST | AFTER column_name ],
  ADD new_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 add to 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 a MySQL table using the Alter Table statement.

ALTER TABLE contacts
  ADD last_name varchar(40) NOT NULL
    AFTER contact_id,
  ADD first_name varchar(35) NULL
    AFTER last_name;

In the above example MySQL Alter Table will add two columns to the contacts table called last_name and first_name.

Add a Single New Columns To Existing Table

Now let’s add a new column in existing table, so here we will use the ALTER TABLE ADD COLUMN statement as I have mentioned below.

ALTER TABLE table
ADD [COLUMN] column_name column_definition [FIRST|AFTER existing_column];

Here :

• I have specify the table name after the ALTER TABLE clauses.
• Define the new column with column definition after the ADD COLUMN clause. You can also skip the COLUMN statement because it is optional.
• FIRST keyword allow us to add new column on the first column of the table. If you want add column after existing column use the AFTER existing_column clause. If do not define these clause it will by default add new column at the last column.

MySQL Add Column More Examples

Now let’s have a look on more examples to add column in MySQL:

1. Create a table named ” sales” as a example. Follow this below MySQL command:

CREATE TABLE IF NOT EXISTS sales (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255)
);

2. Let’s add a new columns named “address” to the “sales” table. Here I am defining the position of the address column after the name column.

ALTER TABLE sales
ADD COLUMN address VARCHAR(15) AFTER name;

3. I am going to add new column named “sales_group” to the sales table. You noticed at this time I did not mention new column’s position as said earlier by default MySQL will add the column at the last in the sales table.

ALTER TABLE sales
ADD COLUMN sales_group INT NOT NULL;

Now Let’s insert the some row into the sales table.

INSERT INTO sales(name,address,sales_group)
VALUES('IBM','(408)-298-2987',1);

INSERT INTO sales(name,address,sales_group)
VALUES('Microsoft','(408)-298-2988',1);

I am going to query the data to the sales table to the all changes.

SELECT 
    id, name, address, sales_group
FROM
    sales;

Let’s add two more columns with the name “phone” and “rate” to the sales tables.

ALTER TABLE sales
ADD COLUMN phone VARCHAR(100) NOT NULL,
ADD COLUMN rate decimal(10,2) NOT NULL;

You noticed here I mentioned NOT NULL value, However the sales table has already data. In this situation MySQL will use default value for these two new columns.

Check the data in the sales table.

SELECT 
    id, name, address, sales_group, phone, rate
FROM
    sales;

In the output you can see the phone value is Blank, not the null value and rate value will be 00.00.

Suppose you added a new column accidentally that already exists in the table, so will get the error msg like below :

ALTER TABLE sales
ADD COLUMN sales_group INT NOT NULL;

Output Error Message :

Error Code: 1060. Duplicate column name 'sales_group'

If you have few tables in your database, it is easy to identify which columns are already present in the table. If there are lots of table with many columns it is will be very difficult to identify that which columns are already present.

As a solution to avoid this error message first you should check wether a column is existing or not before adding new one in it. However there is no statement like ADD COLUMN IF NOT EXITST available. So you can get this information from the columns table of the information_schema database as shown below:

SELECT 
    IF(count(*) = 1, 'Exist','Not Exist') AS result
FROM
    information_schema.columns
WHERE
    table_schema = 'classicmodels'
        AND table_name = 'sales'
        AND column_name = 'address';

I used WHERE clause, to pass three arguments, table schema, database, table name and column name. here I also used IF statement to check column exist or not.

Hope this tutorial helped you to learn How to  Alter Table or Add Multiple columns in Table in MySQL.

FAQs

How can I add multiple columns in a existing table in MySQL?

Follow the below syntax to add multiple columns in table.

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

How can I add a single column to a table in MySQL?

Use this below mysql statement to add a single column in a table in MySQL.

ALTER TABLE table
ADD [COLUMN] column_name column_definition [FIRST|AFTER existing_column];

How can I add a column in a existing table in MySQL?

We can use ALTER TABLE statement to add, delete or modify the column in a existing table.