MySQL: INSERT … SELECT with Same Table

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.)

About Sandro Groganz

Sandro Groganz is an expert in the field of marketing open-source products. He co-founded Age of Peers, a global communications agency for organizations in Open Source. He served as Head of Marketing at Magnolia, creator of the open web content management system Magnolia CMS, Vice President of Marketing at Mindquarry, an open source startup financed by Hasso Plattner Ventures, and Vice President of Communication at eZ Systems, the creator of the open source content management system eZ Publish.

Facebook Comments