In this post, we will discuss how to add columns into the table in MySQL and how to delete existing columns from the table in MySQL database.

Prerequisite: MySQL Table

Add Columuns to the Table

The general MySQL syntax to add a single column into a table is

Note: Syntax within square brackets is optional.

where,

  • table_name is the name of the table.
  • column_definition is the syntax for the new column.
  • FIRST allows to add a new column as the first column of the MySQL table.
  • AFTER existing_column is used to add a new column after a particular existing_column in MySQL table.

If neither of FIRST and AFTER is specified then the new column will be inserted at the last of the MySQL table.

The general syntax for the column_definition used in the add column ALTER statement is

If you need to add multiple new columns to the table then you should use the following syntax

Let’s see some examples for better understanding.

Examples

We will add new columns to the following existing table named ‘pp_posts’.

add column in MySQL

Example 1: Adding a new column ‘content’ to ‘pp_posts’ table.

Add single column in MySQL

Example 2: Adding a new column ‘date’ after column ‘id’.

Add after a column in MySQL

Example 3: Adding multiple columns to table ‘pp_posts’ with single ALTER statement.

Add multiple columns in MySQL

Delete Existing Columuns from the Table

The general syntax to delete or drop existing columns from the table in MySQL is

Note: Syntax within square brackets is optional.

where,

  • table_name is the name of the table.
  • column_name is the name of the column.

If you need to delete multiple columns at the same time using single ALTER statement then use the following syntax

Examples

Here are some example where we are deleting existing columns from the table ‘pp_posts’

Already existing table in MySQL

Example 1: Deleting single column ‘date’.

Delete single column in MySQL

Example 2: Deleting columns ‘category’ and ‘author’ using single ALTER statement.

Delete multiple columns in MySQL

Note: When you try deleting a column having a Foreign Key constraint, then MySQL throws an error. In that case, drop the foreign key constraint then try deleting the column.

I hope now you will be able to add and delete columns in MySQL using ALTER statement.

If you have any doubts or suggestion then please comment below.

Leave a Reply