The latest release MySQL 4.0.14 introduced a neat new feature:
Enabled `INSERT’ from `SELECT’ when the table into which the records are inserted is also a table listed in the `SELECT’.
This means you can now issue a statement like
INSERT INTO table ( field ) SELECT field FROM table WHERE field = “data”;
which copies all records meeting the specified criteria to the same table.
The MySQL manual only states the behaviour previous to 4.0.14:
The target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query. (The problem is that the SELECT possibly would find records that were inserted earlier during the same run. When using subquery clauses, the situation could easily be very confusing.)