Content-Type: text/plain;
  charset="us-ascii"
From: Ian Barwick <barwick@gmx.net>
To: pgsql-sql@postgresql.org
Subject: INSERT INTO ... SELECT (PostgreSQL vs. MySQL)
Date: Thu, 10 Apr 2003 16:27:43 +0200
User-Agent: KMail/1.4.3
MIME-Version: 1.0
Message-Id: <200304101627.43804.barwick@gmx.net>
Content-Transfer-Encoding: quoted-printable


I'm currently "porting" a smallish application from Postgres
to MySQL [*]. I see that with MySQL it is not possible to perform=20

  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 =3D 1

MySQL says: ERROR 1066: Not unique table/alias: 'foo'

This statement works as expected in both PostgreSQL (at least 7.3.x)=20
and also in Oracle 8i.

The MySQL manual says:

  "The target table of the INSERT statement cannot appear in the=20
  FROM clause of the SELECT part of the query because it's forbidden=20
  in standard SQL to SELECT from the same table into which you are=20
  inserting. (The problem is that the SELECT possibly would find
  records that were inserted earlier during the same run.=20
  When using subquery clauses, the situation could easily be very=20
  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 have probably committed some very heinous deed in a previous life ;-)


Ian Barwick
barwick@gmx.net