Selecting a Result Set into an Existing Table

3.22.1 Problem

You want to run a SELECT query but save the results into another table rather than displaying them.

3.22.2 Solution

If the other table exists, use INSERT INTO ... SELECT, described here. If the table doesn't exist, skip ahead to Recipe 3.23.

3.22.3 Discussion

The MySQL server normally returns the result of a SELECT statement to the client that issued the statement. For example, when you run a query from within mysql, the server returns the result to mysql, which in turn displays it to you on the screen. It's also possible to send the results of a SELECT statement directly into another table. Copying records from one table to another is useful in a number of ways:

This section shows how to use INSERT ... SELECT to retrieve a result set for insertion into an existing table. The next section discusses CREATE TABLE ... SELECT, a statement available as of MySQL 3.23 that allows you to create a table on the fly directly from a query result. The table names src_tbl and dst_tbl in the examples refer to the source table from which rows are selected and the destination table into which they are stored.

If the destination table already exists, use INSERT ... SELECT to copy the result set into it. For example, if dst_tbl contains an integer column i and a string column s, the following statement copies rows from src_tbl into dst_tbl, assigning column val to i and column name to s:

INSERT INTO dst_tbl (i, s) SELECT val, name FROM src_tbl;

The number of columns to be inserted must match the number of selected columns, and the correspondence between sets of columns is established by position rather than name. In the special case that you want to copy all columns from one table to another, you can shorten the statement to this form:

INSERT INTO dst_tbl SELECT * FROM src_tbl;

To copy only certain rows, add a WHERE clause that selects the rows you want:

INSERT INTO dst_tbl SELECT * FROM src_tbl WHERE val > 100 AND name LIKE 'A%';

It's not necessary to copy column values without modification from the source table into the destination table. The SELECT statement can produce values from expressions, too. For example, the following query counts the number of times each name occurs in src_tbl and stores both the counts and the names in dst_tbl:

INSERT INTO dst_tbl (i, s) SELECT COUNT(*), name FROM src_tbl GROUP BY name;

When you use INSERT ... SELECT, you cannot use the same table both as a source and a destination.

Категории