X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id DE5F54762A2
	for <pgsql-sql@postgresql.org>; Thu, 10 Apr 2003 11:01:11 -0400 (EDT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178])
	by postgresql.org (Postfix) with ESMTP id 5ABA0475FC8
	for <pgsql-sql@postgresql.org>; Thu, 10 Apr 2003 11:01:07 -0400 (EDT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id CC3D8D63F; Thu, 10 Apr 2003 08:01:11 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id C1C245C0A; Thu, 10 Apr 2003 08:01:11 -0700 (PDT)
Date: Thu, 10 Apr 2003 08:01:11 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Ian Barwick <barwick@gmx.net>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: INSERT INTO ... SELECT (PostgreSQL vs. MySQL)
In-Reply-To: <200304101627.43804.barwick@gmx.net>
Message-ID: <20030410075821.F80096-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Spam-Status: No, hits=-26.4 required=5.0
	tests=BAYES_00,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT,
	QUOTE_TWICE_1,REPLY_WITH_QUOTES autolearn=ham	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200304/162
X-Sequence-Number: 12863

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