GNU Info

(mysql.info)INSERT SELECT

---

Prev: INSERT Up: INSERT

---

Enter node , (file) or (file)node

`INSERT ... SELECT' Syntax
..........................

     INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column list)] SELECT ...

With `INSERT ... SELECT' statement you can quickly insert many rows
into a table from one or many tables.

     INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE
     tblTemp1.fldOrder_ID > 100;

The following conditions hold for an `INSERT ... SELECT' statement:

   - The target table of the `INSERT' statement cannot appear in the
     `FROM' clause of the `SELECT' part of the query because it's
     forbidden in ANSI SQL to `SELECT' from the same table into which
     you are inserting.  (The problem is that the `SELECT' possibly
     would find records that were inserted earlier during the same run.
     When using sub-select clauses, the situation could easily be very
     confusing!)

   - `AUTO_INCREMENT' columns work as usual.

   - You can use the C API function `mysql_info()' to get information
     about the query. Note: INSERT.

   - To ensure that the update log/binary log can be used to re-create
     the original tables, MySQL will not allow concurrent inserts during
     `INSERT .... SELECT'.

You can of course also use `REPLACE' instead of `INSERT' to overwrite
old rows.