| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top] | [Contents] | [Index] | [ ? ] |
INSERT ... SELECT Syntax INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column list)]
SELECT ...
|
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 an INSERT ... SELECT statement:
Prior to MySQL 4.0.1, INSERT ... SELECT implicitly operates in
IGNORE mode. As of MySQL 4.0.1, specify IGNORE
explicitly to ignore records that would cause duplicate-key violations.
Do not use DELAYED with INSERT ... SELECT.
Prior to MySQL 4.0.14,
the target table of the INSERT statement cannot appear in the
FROM clause of the SELECT part of the query.
This limitation is lifted in 4.0.14.
AUTO_INCREMENT columns work as usual.
To ensure that the binary log can be used to re-create the
original tables, MySQL will not allow concurrent inserts during
INSERT ... SELECT.
You can use REPLACE instead of INSERT to overwrite old rows.
REPLACE is the counterpart to INSERT IGNORE in the treatment
of new rows that contain unique key values that duplicate old rows:
The new rows are used to replace the old rows rather than being discarded.
This document was generated on July, 26 2004 using texi2html 1.70.