Re: INSERT INTO ... SELECT (PostgreSQL vs. MySQL)

Ian Barwick <barwick(at)gmx(dot)net> writes:
> The MySQL manual says:

> "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 standard 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 subquery clauses, the situation could easily be very
> confusing!)"

> Can anyone shed light on whether the above statement (especially
> the bit about "standard SQL") is correct?

As usual ;-), the MySQL guys shade the truth to suit themselves.

The body of the spec's description of INSERT INTO clearly allows this
operation. SQL92 says

3) The <query expression> is effectively evaluated before inserting
any rows into B.

which SQL99 renders as

5) QT is effectively evaluated before insertion of any rows into T.

so they have a perfectly clear model of how it should work. MySQL's
explanation of why it's undefined is just an explanation of why their
implementation cannot support it.

It is true that this is considered an advanced feature. SQL92
classifies it as Full SQL, while SQL99 calls it Feature F781:

Leveling Rules

1) The following restrictions apply for Intermediate SQL:

a) The leaf generally underlying table of T shall not be gen-
erally contained in the <query expression> immediately
contained in the <insert columns and source> except as the
<qualifier> of a <column reference>.

resp.

Conformance Rules

1) Without Feature F781, "Self-referencing operations", no leaf
generally underlying table of T shall be generally contained in
the <query expression> immediately contained in the <insert
columns and source> except as the <table or query name> or
<correlation name> of a column reference.

At least, I *think* this is what these restrictions are talking about.
I'm not sure what the "except" phrases purport to allow (in standard
SQL it's not meaningful to reference a table not mentioned in FROM,
so what are they giving permission for here??). Maybe these
restrictions are talking about something else entirely? But there
is no other part of the spec that could possibly be read MySQL's way.

regards, tom lane