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
IGNOREto ignore rows that would cause duplicate-key violations.
DELAYEDis ignored with
INSERT ... SELECT.
The target table of the
INSERTstatement may appear in the
FROMclause of the
SELECTpart of the query. (This was not possible in some older versions of MySQL.) However, you cannot insert into a table and select from the same table in a subquery.
When selecting from and inserting into a table at the same time, MySQL creates a temporary table to hold the rows from the
SELECTand then inserts those rows into the target table. However, it remains true that you cannot use
INSERT INTO t ... SELECT ... FROM twhen
TEMPORARYtables cannot be referred to twice in the same statement (see Section B.5.6.2, “TEMPORARY Table Problems”).
AUTO_INCREMENTcolumns work as usual.
To avoid ambiguous column reference problems when the
INSERTrefer to the same table, provide a unique alias for each table used in the
SELECTpart, and qualify column names in that part with the appropriate alias.
Starting with MySQL 5.6.2, you can explicitly select which
partitions or subpartitions (or both) of the source or target
table (or both) are to be used with a
PARTITION option following the name of the
PARTITION is used with the name
of the source table in the
portion of the statement, rows are selected only from the
partitions or subpartitions named in its partition list. When
PARTITION is used with the name of the target
table for the
INSERT portion of
the statement, then it must be possible to insert all rows
selected into the partitions or subpartitions named in the
partition list following the option, else the
... SELECT statement fails. For more information and
examples, see Section 19.5, “Partition Selection”.
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.
The order in which rows are returned by a
SELECT statement with no
ORDER BY clause is not determined. This means
that, when using replication, there is no guarantee that such a
SELECT returns rows in the same order on the
master and the slave; this can lead to inconsistencies between
them. To prevent this from occurring, you should always write
INSERT ... SELECT statements that are to be
INSERT ... SELECT ... ORDER BY
. The choice of
column does not matter as long as the
same order for returning the rows is enforced on both the master
and the slave. See also
Section 220.127.116.11, “Replication and LIMIT”.
Due to this issue, beginning with MySQL 5.6.4,
SELECT ON DUPLICATE KEY UPDATE and
INSERT IGNORE ...
SELECT statements are flagged as unsafe for
statement-based replication. With this change, such statements
produce a warning in the log when using statement-based mode and
are logged using the row-based format when using
MIXED mode. (Bug #11758262, Bug #50439)
Prior to MySQL 5.6.6, an
INSERT ... SELECT
statement that acted on partitioned tables using a storage
engine such as
MyISAM that employs
table-level locks locked all partitions of the source and target
tables. (This did not and does not occur with tables using
storage engines such as
employ row-level locking.) In MySQL 5.6.6 and later, only those
partitions of the source table that are actually read are
locked, although all partitions of the target table are locked.
See Section 19.6.4, “Partitioning and Locking”, for more