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

On Thu, 10 Apr 2003, Ian Barwick wrote:

>
> I'm currently "porting" a smallish application from Postgres
> to MySQL [*]. I see that with MySQL it is not possible to perform
>
> INSERT INTO ... SELECT
>
> when the target table is the same as the source table, e.g.
>
> INSERT INTO foo (abc, xyz)
> SELECT abc, xyz FROM foo WHERE id = 1
>
> MySQL says: ERROR 1066: Not unique table/alias: 'foo'
>
> This statement works as expected in both PostgreSQL (at least 7.3.x)
> and also in Oracle 8i.
>
> 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!)"
>
> ( http://www.mysql.com/doc/en/INSERT_SELECT.html )
>
> Can anyone shed light on whether the above statement (especially
> the bit about "standard SQL") is correct? I can't get my head
> around MySQL being more standards compliant than Postgres here...

I'm guessing they're speaking of (13.8 leveling rules 1)

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

I think when they mention the spec. :)

However, that's a leveling rule, so it's optional (if you don't support
it you can't claim the full level, you can only claim Intermediate SQL).