How to add multiple column in MySQL?

Question

This article will be looking into how to add multiple columns after the last or a specific column in MySQL.

Table of Contents Show

MySQL- how to add multiple columns after the last column
MySQL -how to add multiple columns after a specific column
MySQL -how to add multiple columns after different columns
Overview of SQL column_name data_type constraint; Code language: SQL (Structured Query Language) (sql)7 clause
SQL column_name data_type constraint; Code language: SQL (Structured Query Language) (sql)7 examples
SQL column_name data_type constraint; Code language: SQL (Structured Query Language) (sql)7 statement in some common database systems
How to add multiple columns at once in MySQL?
How do you add multiple columns?
How do I add multiple columns in MySQL workbench?
How to add another column in MySQL?

Table of Contents

We will be moving into details of each section but let us first make the data to be used across. We will be creating a table students_data followed by inserting a few rows into it.

CREATE TABLE students_data (
student_id INT AUTO_INCREMENT,
student_first_name VARCHAR(255),
student_address VARCHAR(255),
student_grade VARCHAR(255),
student_subject VARCHAR(255),
 PRIMARY KEY (student_id)
);
INSERT INTO students_data (student_first_name, student_address, student_grade, student_subject) 
VALUES("Gustav","56A Denmark","A","Physics"),
("Henric","255 USA","B","Geography"),
("Richa","78 India","C","Physics"),
("Margit","58 Canada","A","Physics"),	
("Rasmus","18 Libya","B","Physics"),
("Erick","43 Sweden","C","Geography"),
("Tanya","78 Singapore","D","Geography")

SELECT * FROM students_data;

figure 1

MySQL- how to add multiple columns after the last column

In this section, let us see how to add multiple columns to a table. By default, new columns are added after the last column in the table. Observe the below query.

ALTER TABLE students_data 
ADD COLUMN student_gender VARCHAR(50) ,
ADD COLUMN  student_email VARCHAR(255),    
ADD COLUMN  student_age INT;

Action Output Message Response:-

MySQL -how to add multiple columns after a specific column

This section lets us see how to add multiple columns to a table after a specific column. Observe the below query.

ALTER TABLE students_data 
ADD COLUMN student_gender VARCHAR(50) ,
ADD COLUMN  student_email VARCHAR(255),    
ADD COLUMN  student_age INT AFTER student_grade;

Action Output Message Response:-

0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0

Let us check if the columns got added by executing:

SELECT * FROM students_data LIMIT 1;

Output:-

figure 3

Figure 3 shows that three new columns, student_gender, student_email, and student_age, got added with null values to the students_data table.

student_age got added after student_grade and student_gender, student_email got added after the last column of the table students_data.

MySQL -how to add multiple columns after different columns

In this section, let us see how to add multiple columns to a table after different columns. Observe the below query.

ALTER TABLE students_data 
ADD COLUMN student_last_name VARCHAR(255) AFTER student_first_name,
ADD COLUMN student_email VARCHAR(255)AFTER student_last_name, 
ADD COLUMN student_gender VARCHAR(50) AFTER student_email,
ADD COLUMN student_age INT AFTER student_subject;

Action Output Message Response:-

0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0

Let us check if the columns got added to the students_data table by executing:

SELECT * FROM students_data LIMIT 1;

figure 4

Figure 4 shows that four new columns student_last_name, student_email, student_gender and student_age got added with null values to the students_data table.

student_last_name is added after student_first_name. student_email is added after student_last_name. student_gender is added after student_email and student_age after student_subject.

Summary: in this tutorial, you will learn how to use the SQL

column_name data_type constraint;
Code language: SQL (Structured Query Language) (sql)

7 clause of the

column_name data_type constraint;
Code language: SQL (Structured Query Language) (sql)

8 statement to add one or more columns to an existing table.

Overview of SQL column_name data_type constraint; Code language: SQL (Structured Query Language) (sql)7 clause

To add a new column to a table, you use the

ALTER TABLE table_name
ADD [COLUMN] column_definition,
ADD [COLUMN] column_definition,
 ...;
Code language: SQL (Structured Query Language) (sql)

0 statement as follows:

ALTER TABLE table_name
ADD [COLUMN] column_definition;
Code language: SQL (Structured Query Language) (sql)

In this statement,

First, specify the table to which you want to add the new column.

Second, specify the column definition after the

column_name data_type constraint;



Code language: SQL (Structured Query Language) (sql)

7 clause.

The typical syntax of the

ALTER TABLE table_name
ADD [COLUMN] column_definition,
ADD [COLUMN] column_definition,
 ...;
Code language: SQL (Structured Query Language) (sql)

2 is as follows:

column_name data_type constraint;
Code language: SQL (Structured Query Language) (sql)

If you want to add multiple columns to an existing table using a single statement, you use the following syntax:

ALTER TABLE table_name
ADD [COLUMN] column_definition,
ADD [COLUMN] column_definition,
 ...;
Code language: SQL (Structured Query Language) (sql)

Different database systems support the

ALTER TABLE table_name
ADD [COLUMN] column_definition,
ADD [COLUMN] column_definition,
 ...;
Code language: SQL (Structured Query Language) (sql)

0 statement with some minor variances. Please check it out the next section for references.

SQL column_name data_type constraint; Code language: SQL (Structured Query Language) (sql)7 examples

The following statement creates a new table named

ALTER TABLE table_name
ADD [COLUMN] column_definition,
ADD [COLUMN] column_definition,
 ...;
Code language: SQL (Structured Query Language) (sql)

5:

CREATE TABLE candidates (
    id INT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);
Code language: SQL (Structured Query Language) (sql)

In order to add the

ALTER TABLE table_name
ADD [COLUMN] column_definition,
ADD [COLUMN] column_definition,
 ...;
Code language: SQL (Structured Query Language) (sql)

6 column to the

ALTER TABLE table_name
ADD [COLUMN] column_definition,
ADD [COLUMN] column_definition,
 ...;
Code language: SQL (Structured Query Language) (sql)

5 table, you use the following statement:

ALTER TABLE candidates
ADD COLUMN phone VARCHAR(50);
Code language: SQL (Structured Query Language) (sql)

To add three columns: home address, date of birth, and linkedin account to the

ALTER TABLE table_name
ADD [COLUMN] column_definition,
ADD [COLUMN] column_definition,
 ...;



Code language: SQL (Structured Query Language) (sql)

5 table, you use the following statement:

ALTER TABLE candidates
ADD COLUMN home_address VARCHAR(255),
ADD COLUMN dob DATE,
ADD COLUMN linkedin_account VARCHAR(255);
Code language: SQL (Structured Query Language) (sql)

SQL column_name data_type constraint; Code language: SQL (Structured Query Language) (sql)7 statement in some common database systems

The following section provides you with the syntax of the

ALTER TABLE table_name
ADD [COLUMN] column_definition,
ADD [COLUMN] column_definition,
 ...;
Code language: SQL (Structured Query Language) (sql)

0 statement in some common database systems.

PostgreSQL

Add one column to a table in PostgreSQL:

ALTER TABLE table_name
ADD COLUMN column_definition;
Code language: SQL (Structured Query Language) (sql)

Add multiple columns to a table in PostgreSQL:

    ALTER TABLE table_name
    ADD COLUMN column_definition,
    ADD COLUMN column_definition,
    ...
    ADD COLUMN column_definition;
Code language: SQL (Structured Query Language) (sql)

MySQL

Add one column to a table in MySQL:

    ALTER TABLE table_name
    ADD [COLUMN] column_definition;
Code language: SQL (Structured Query Language) (sql)

Add multiple columns to a table in MySQL:

    ALTER TABLE table_name
    ADD [COLUMN] column_definition,
    ADD [COLUMN] column_definition,
    ...
    ADD [COLUMN] column_definition;
Code language: SQL (Structured Query Language) (sql)

Oracle

Add one column to a table in Oracle:

column_name data_type constraint;
Code language: SQL (Structured Query Language) (sql)

0

Add multiple columns to a table in Oracle:

column_name data_type constraint;
Code language: SQL (Structured Query Language) (sql)

1

SQL Server

Add one column to a table in SQL Server:

column_name data_type constraint;
Code language: SQL (Structured Query Language) (sql)

0

Add multiple columns to a table in SQL Server:

column_name data_type constraint;
Code language: SQL (Structured Query Language) (sql)

3

SQLite

Add one column to a table in SQLite:

ALTER TABLE table_name
ADD COLUMN column_definition;
Code language: SQL (Structured Query Language) (sql)

SQLite does not support adding multiple columns to a table using a single statement. To add multiple columns to a table, you must execute multiple

ALTER TABLE table_name
ADD [COLUMN] column_definition,
ADD [COLUMN] column_definition,
 ...;
Code language: SQL (Structured Query Language) (sql)

0 statements.

DB2

Add one column to a table in DB2

column_name data_type constraint;
Code language: SQL (Structured Query Language) (sql)

0

Add multiple columns to a table in DB2:

column_name data_type constraint;
Code language: SQL (Structured Query Language) (sql)

6

Notice that there are no commas between columns.

In this tutorial, you have learned about the SQL

column_name data_type constraint;
Code language: SQL (Structured Query Language) (sql)

7 clause of the

column_name data_type constraint;
Code language: SQL (Structured Query Language) (sql)

8 statement to add one or more columns to an existing table.

How to add multiple columns at once in MySQL?

In MySQL, to add a new column to an existing table, the ALTER TABLE syntax would look like this: ALTER TABLE table_name ADD COLUMN column_name column_definition; Let's try adding multiple columns in one command. If you found value in this post, you can show your appreciation and buy me a coffee.

How do you add multiple columns?

Insert columns.

Select the heading of the column to the right of which you want to insert additional columns. Tip: Select the same number of columns as you want to insert. ... .

Hold down CONTROL, click the selected columns, and then on the pop-up menu, click Insert..

How do I add multiple columns in MySQL workbench?

How do I add more columns in MySQL? The syntax to add a column in a table in MySQL (using the ALTER TABLE statement) is: ALTER TABLE table_name ADD new_column_name column_definition [ FIRST | AFTER column_name ]; table_name.

How to add another column in MySQL?

Use ADD to add new columns to a table, and DROP to remove existing columns. DROP col_name is a MySQL extension to standard SQL. To add a column at a specific position within a table row, use FIRST or AFTER col_name . The default is to add the column last.