INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
INSERT ... 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
INSERT ... SELECTstatements:
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 C.5.7.2, “
AUTO_INCREMENTcolumns work as usual.
To ensure that the binary log can be used to re-create the original tables, MySQL does not permit concurrent inserts for
INSERT ... SELECTstatements.
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.
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 BYin the
SELECTpart. One side effect is that you must qualify nonunique column names in the values part.
The order in which rows are returned by a
SELECTstatement with no
ORDER BYclause is not determined. This means that, when using replication, there is no guarantee that such a
SELECTreturns 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 ... SELECTstatements that are to be replicated as
INSERT ... SELECT ... ORDER BY. The choice of
columndoes not matter as long as the same order for returning the rows is enforced on both the master and the slave. See also Section 18.104.22.168, “Replication and