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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s