This documentation is for an older version. If you're using the most current version, select the documentation for that version with the version switch in the upper right corner of the online documentation, or by downloading a newer PDF or EPUB file. INSERT ... SELECT Syntax

    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

With 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 ... SELECT statements:

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 replicated as INSERT ... SELECT ... ORDER BY column. 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, “Replication and LIMIT”.