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)
Date: Thu, 10 Apr 2003 08:01:11 -0700 (PDT)
Message-ID: <20030410075821.F80096-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <200304101627.43804.barwick@gmx.net>
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).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, barwick@gmx.net
Subject: Re: INSERT INTO ... SELECT (PostgreSQL vs. MySQL)
In-Reply-To: <20030410075821.F80096-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox