MYSQL INSERT WITH SELECT

In previous articles we learned to insert data into a table, feel free to checkout the article MYSQL INSERT INTO.

This article will discuss how to insert data into a table by selecting the data from some other source, for example, another table.

Table of Contents

• MYSQL INSERT FROM SELECT: SINGLE TABLE
• MYSQL INSERT FROM SELECT: MULTIPLE TABLES

MYSQL INSERT WITH SELECT: SINGLE TABLE

Let us start by looking into the syntax.

Syntax:-

INSERT INTO tableA (colA1,colA2,colA3…) 
SELECT colB1,colB2,colB3… from tableB   
WHERE condition;

• Here, tableA is the table where we will add rows for column list colA1,colA2,colA3….
• tableB is another table for selection from columns colB1,colB2,colB3…
• WHERE clause is not mandatory, we can always add a condition if we want some specific, condition-based data.

Example:-

To have a better understanding of the concept, we will work through an example. In this example, we will be creating a table registration_details, which will then select some data from an already existing table student_details in our database.

student_details table has columns student_no, first_name, last_name and stream_name. To see all the rows in student_details table we will do a SELECT * query on it.

SELECT * FROM student_details;

Output:-

We can see there are 8 rows of data with five students having ‘Science’ stream. Now, let us create our new table registration_details.

CREATE TABLE registration_details (
    student_no INT AUTO_INCREMENT,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    registration_flag VARCHAR(10) DEFAULT 'ND',
    PRIMARY KEY (student_no)
);

If we do a select * from registration_details, it shows that the table is empty.

SELECT * FROM registration_details;

Output :-

We will proceed by adding rows to registration_details table, we are selecting a few columns (student_no, first_name, last_name) from student_details table. The example scenario is as follows:  registration_details table requires students who belong to stream ‘Science.’ The MySQL query will be :

 INSERT INTO registration_details(student_no,first_name,last_name ) 
 SELECT student_no,first_name,last_name FROM student_details 
 WHERE stream_name = 'Science' ;

Select * on registration_details table to view the added rows.

SELECT * FROM registration_details;

Output :-

Note that only the columns student_no, first_name and last_name have been added. Since our selection was based on a condition, only records satisfying the condition( students with stream_name ‘Science’ ) were added.

MYSQL INSERT WITH SELECT: MULTIPLE TABLES

Like adding rows into a table from a single table, we can also select data from multiple tables.

Example :-

Again, let us understand with the help of an example. We will use table registration_details to add the data from tables student_details and registration_status (already present in the database).

Do a select * on all the three tables one by one to see what data is present in them.

SELECT * FROM registration_details;

Output:-

Please note that we had truncated data from registration_details at the moment. Select * on student_details.

SELECT * FROM student_details;

SELECT * FROM registration_status;

Output:-

We will now write a query in MySQL to get the data into registration_details from tables student_details and registration_status.

Selecting first_name and last_name from student_details and registration_flag from registration_status of only those students who have ‘Science‘ stream.

INSERT INTO registration_details(student_no,first_name,last_name, registration_flag) 
SELECT sd.student_no,sd.first_name,sd.last_name ,rs.registration_flag 
FROM  student_details sd , registration_status rs
WHERE sd.first_name = rs.first_name 
AND sd.last_name = rs.last_name
AND sd.stream_name = 'Science'  ;

The above query is a bit complex as we want to make sure that the correct status of registration_flag is loaded, and hence we are matching the first_name and last_ name of both the tables student_details and registration_status. Also, note that sd and rs here are aliases for student_details and registration_status tables, respectively.

Once the query is executed, we will look into the records of registration_details to make sure that we added the rows from more than one table.

SELECT * FROM registration_details;

Output :-

So, the rows have been added to registration_details successfully.

In this article we learned how to insert data to a table making selections from one or more than one tables. We hope this article helped you to have a better understanding on the concept of INSERT SELECT FROM.