MySQL: Use INSERT INTO From Another Table


You can use the following syntax in MySQL to insert rows from another table:

INSERT INTO athletes1 (athleteID, team, points)  
SELECT athleteID, team_name, total_points
FROM `athletes2`;

This particular example inserts the rows from the table named athletes2 for the columns named athleteID, team_name and total_points into the table named athletes1.

The following example shows how to use this syntax in practice.

Example: Use INSERT INTO From Another Table in MySQL

Suppose we create the following table named athletes1 that contains information about various basketball players:

-- create table 
CREATE TABLE athletes1 (
  athleteID INT PRIMARY KEY,
  team TEXT NOT NULL,
  points INT NOT NULL
);

-- insert rows into table
INSERT INTO athletes1 VALUES (0001, 'Mavs', 22);
INSERT INTO athletes1 VALUES (0002, 'Warriors', 14);
INSERT INTO athletes1 VALUES (0003, 'Nuggets', 37);

-- view all rows in table
SELECT * FROM athletes1;

Output:

+-----------+----------+--------+
| athleteID | team     | points |
+-----------+----------+--------+
|         1 | Mavs     |     22 |
|         2 | Warriors |     14 |
|         3 | Nuggets  |     37 |
+-----------+----------+--------+

Then suppose we create another table named athletes2 that contains information about more basketball players:

-- create table 
CREATE TABLE athletes2 (
  athleteID INT PRIMARY KEY,
  team_name TEXT NOT NULL,
  total_points INT NOT NULL
);

-- insert rows into table
INSERT INTO athletes2 VALUES (0004, 'Lakers', 19);
INSERT INTO athletes2 VALUES (0005, 'Celtics', 26);
INSERT INTO athletes2 VALUES (0006, 'Thunder', 40);

-- view all rows in table
SELECT * FROM athletes2;

Output:

+-----------+-----------+--------------+
| athleteID | team_name | total_points |
+-----------+-----------+--------------+
|         4 | Lakers    |           19 |
|         5 | Celtics   |           26 |
|         6 | Thunder   |           40 |
+-----------+-----------+--------------+

Suppose that we would like to insert all of the rows from athletes2 into athletes1.

We can use the following syntax to do so:

-- insert rows from athletes2 into athletes1
INSERT INTO athletes1 (athleteID, team, points)  
SELECT athleteID, team_name, total_points
FROM `athletes2`;

-- view all rows in athletes1 table
SELECT * FROM athletes1;

+-----------+----------+--------+
| athleteID | team     | points |
+-----------+----------+--------+
|         1 | Mavs     |     22 |
|         2 | Warriors |     14 |
|         3 | Nuggets  |     37 |
|         4 | Lakers   |     19 |
|         5 | Celtics  |     26 |
|         6 | Thunder  |     40 |
+-----------+----------+--------+

Notice that all rows from athletes2 have been inserted into athletes1.

If you’d like, you could also use the WHERE clause to only insert specific rows from athletes2.

For example, we can use the following syntax to only insert the rows from athletes2 where the value in the total_points column is greater than 20:

-- insert rows from athletes2 into athletes1
INSERT INTO athletes1 (athleteID, team, points)  
SELECT athleteID, team_name, total_points
FROM `athletes2`
WHERE total_points > 20;

-- view all rows in athletes1 table
SELECT * FROM athletes1;

+-----------+----------+--------+
| athleteID | team     | points |
+-----------+----------+--------+
|         1 | Mavs     |     22 |
|         2 | Warriors |     14 |
|         3 | Nuggets  |     37 |
|         5 | Celtics  |     26 |
|         6 | Thunder  |     40 |
+-----------+----------+--------+

Notice that only the rows from athletes2 with a value greater than 20 in the total_points column have been inserted into athletes1.

Additional Resources

The following tutorials explain how to perform other common tasks in MySQL:

MySQL: How to Add Column with Default Value
MySQL: How to Add Column After a Specific Column
MySQL: How to Add Auto-Increment Column to Existing Table

Leave a Reply

Your email address will not be published. Required fields are marked *