Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.1Mb
PDF (A4) - 35.2Mb
Man Pages (TGZ) - 256.3Kb
Man Pages (Zip) - 361.2Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  / ...  / SQL Statements  / Data Manipulation Statements  / INSERT Statement  /  INSERT ... SELECT Statement

13.2.5.1 INSERT ... SELECT Statement

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]    [INTO]tbl_name    [PARTITION (partition_name [,partition_name] ...)]    [(col_name [,col_name] ...)]    SELECT ...    [ON DUPLICATE KEY UPDATEassignment_list]value:    {expr | DEFAULT}assignment:col_name =valueassignment_list:assignment [,assignment] ...

WithINSERT ... SELECT, you can quickly insert many rows into a table from the result of aSELECT statement, which can select 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 forINSERT ... SELECT statements:

  • SpecifyIGNORE to ignore rows that would cause duplicate-key violations.

  • The target table of theINSERT statement may appear in theFROM clause of theSELECT part of the query. However, you cannot insert into a table and select from the same table in a subquery.

    When selecting from and inserting into the same table, MySQL creates an internal temporary table to hold the rows from theSELECT and then inserts those rows into the target table. However, you cannot useINSERT INTO t ... SELECT ... FROM t whent is aTEMPORARY table, becauseTEMPORARY tables cannot be referred to twice in the same statement. SeeSection 8.4.4, “Internal Temporary Table Use in MySQL”, andSection B.3.6.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 forINSERT ... SELECT statements (seeSection 8.11.3, “Concurrent Inserts”).

  • To avoid ambiguous column reference problems when theSELECT and theINSERT refer to the same table, provide a unique alias for each table used in theSELECT part, and qualify column names in that part with the appropriate alias.

You can explicitly select which partitions or subpartitions (or both) of the source or target table (or both) are to be used with aPARTITION clause following the name of the table. WhenPARTITION is used with the name of the source table in theSELECT portion of the statement, rows are selected only from the partitions or subpartitions named in its partition list. WhenPARTITION is used with the name of the target table for theINSERT portion of the statement, it must be possible to insert all rows selected into the partitions or subpartitions named in the partition list following the option. Otherwise, theINSERT ... SELECT statement fails. For more information and examples, seeSection 22.5, “Partition Selection”.

ForINSERT ... SELECT statements, seeSection 13.2.5.2, “INSERT ... ON DUPLICATE KEY UPDATE Statement” for conditions under which theSELECT columns can be referred to in anON DUPLICATE KEY UPDATE clause.

The order in which aSELECT statement with noORDER BY clause returns rows is nondeterministic. This means that, when using replication, there is no guarantee that such aSELECT returns rows in the same order on the source and the replica, which can lead to inconsistencies between them. To prevent this from occurring, always writeINSERT ... SELECT statements that are to be replicated using anORDER BY clause that produces the same row order on the source and the replica. See alsoSection 16.4.1.17, “Replication and LIMIT”.

Due to this issue,INSERT ... SELECT ON DUPLICATE KEY UPDATE andINSERT IGNORE ... SELECT statements are flagged as unsafe for statement-based replication. Such statements produce a warning in the error log when using statement-based mode and are written to the binary log using the row-based format when usingMIXED mode. (Bug #11758262, Bug #50439)

See alsoSection 16.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.

AnINSERT ... SELECT statement affecting partitioned tables using a storage engine such asMyISAM that employs table-level locks locks all partitions of the target table; however, only those partitions that are actually read from the source table are locked. (This does not occur with tables using storage engines such asInnoDB that employ row-level locking.) For more information, seeSection 22.6.4, “Partitioning and Locking”.