INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO]
col_name,...)] SELECT ... [ ON DUPLICATE KEY UPDATE
expr, ... ]
SELECT, you can quickly insert many rows into a table
from one or many tables. For example:
INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
The following conditions hold for a
Prior to MySQL 4.0.1,
SELECT implicitly operates in
IGNORE mode. As of MySQL 4.0.1, specify
IGNORE explicitly to ignore rows that
would cause duplicate-key violations.
DELAYED is ignored with
Prior to MySQL 4.0.14, the target table of the
INSERT statement cannot
appear in the
FROM clause of the
SELECT part of the query.
This limitation is lifted in 4.0.14. In this case, MySQL
creates a temporary table to hold the rows from the
SELECT and then inserts those
rows into the target table. However, it remains true that
you cannot use
INSERT INTO t ... SELECT ... FROM
t is a
TEMPORARY table, because
TEMPORARY tables cannot be referred to
twice in the same statement (see
Section B.5.7.2, “
TEMPORARY Table Problems”).
AUTO_INCREMENT columns work as usual.
To ensure that the binary log can be used to re-create the
original tables, MySQL does not permit concurrent inserts
... SELECT statements.
Currently, you cannot insert into a table and select from the same table in a subquery.
To avoid ambiguous column reference problems when the
SELECT and the
INSERT refer to the same
table, provide a unique alias for each table used in the
SELECT part, and qualify
column names in that part with the appropriate alias.
In the values part of
ON DUPLICATE KEY
UPDATE, you can refer to columns in other tables, as
long as you do not use
GROUP BY in the
SELECT part. One side effect is
that you must qualify nonunique column names in the values part.