MySQL INSERT SELECT

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

VALUES supplies rows you type. A SELECT in its place supplies rows the server works out, so a summary you can query is a summary you can store. It is how a report table gets filled and how rows move from one table to another. These examples use the sakila_practice database from INSERT. Run the setup block there again before you start, then open the client with mysql -u root -p sakila_practice.

How INSERT SELECT works

INSERT INTO TARGET_TABLE (COLUMN_LIST)
SELECT SOURCE_COLUMNS FROM SOURCE_TABLE;

Replace TARGET_TABLE with the table being written to, COLUMN_LIST with its columns, SOURCE_COLUMNS with what the query returns, and SOURCE_TABLE with the table being read. There is no VALUES keyword. The columns pair up by position, so the first thing the SELECT returns goes into the first column named, whatever the 2 are called. Every row the query returns becomes a row in the target table.

Examples

1) Store a summary

rating_count is empty and sakila.film holds the answer. Naming a table database.table reads it from another database, so 1 statement can cross between the 2.

INSERT INTO rating_count (rating, films)
SELECT rating, COUNT(*) FROM sakila.film GROUP BY rating;

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

The GROUP BY produced 5 rows and 5 rows were written.

SELECT * FROM rating_count ORDER BY films DESC;

+--------+-------+
| rating | films |
+--------+-------+
| PG-13  |   223 |
| NC-17  |   210 |
| R      |   195 |
| PG     |   194 |
| G      |   178 |
+--------+-------+
5 rows in set (0.00 sec)

These are ordinary rows now, and they are a snapshot. Nothing updates them when film changes.

2) Build values on the way in

The SELECT can compute anything, so the rows going in do not have to exist anywhere in that shape.

INSERT INTO member (first_name, last_name, email, joined)
SELECT first_name, last_name, CONCAT(LOWER(first_name), '.', LOWER(last_name), '@example.net'), '2026-05-01'
FROM sakila.staff;

Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

SELECT member_id, first_name, last_name, email FROM member WHERE joined = '2026-05-01';

+-----------+------------+-----------+--------------------------+
| member_id | first_name | last_name | email                    |
+-----------+------------+-----------+--------------------------+
|         4 | Mike       | Hillyer   | mike.hillyer@example.net |
|         5 | Jon        | Stephens  | jon.stephens@example.net |
+-----------+------------+-----------+--------------------------+
2 rows in set (0.00 sec)

The address column was made by CONCAT and the date is a constant repeated on every row.

ERROR 1136 means the 2 lists are different lengths

INSERT INTO rating_count (rating, films)
SELECT rating FROM sakila.film GROUP BY rating;

ERROR 1136 (21S01): Column count doesn't match value count at row 1

2 columns named, 1 returned. The 2 lists always differ in length when this error appears, so count them. Nothing checks the names or the meanings, so a SELECT that returns the right number of the wrong columns is accepted and writes nonsense. Run the SELECT on its own first and read what comes back.

A second run meets the table’s keys

Every row the query returns is offered to the table, and the table’s rules still apply.

INSERT INTO rating_count (rating, films)
SELECT rating, COUNT(*) FROM sakila.film WHERE rating = 'G' GROUP BY rating;

ERROR 1062 (23000): Duplicate entry 'G' for key 'rating_count.PRIMARY'

rating is the primary key and G is already stored. A summary you want to refresh rather than build once needs upsert, which updates the stored row instead of failing.

Summary

Put a SELECT where VALUES would go, and write no VALUES keyword.
Expect the columns to pair up by position, never by name.
Read ERROR 1136 as 2 lists of different lengths.
Name a table database.table to read from another database in the same statement.
Expect a second run to hit the target table’s keys, the same as any other insert.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How INSERT SELECT works

Examples

1) Store a summary

2) Build values on the way in

ERROR 1136 means the 2 lists are different lengths

A second run meets the table’s keys

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How INSERT SELECT works

​Examples

​1) Store a summary

​2) Build values on the way in

​ERROR 1136 means the 2 lists are different lengths

​A second run meets the table’s keys

​Summary

​See also

How INSERT SELECT works

Examples

1) Store a summary

2) Build values on the way in

ERROR 1136 means the 2 lists are different lengths

A second run meets the table’s keys

Summary

See also