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

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