agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

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
Message-ID: <200304101627.43804.barwick@gmx.net> (raw)


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 have probably committed some very heinous deed in a previous life ;-)


Ian Barwick
barwick@gmx.net

view thread (7+ messages)  latest in thread

Message-ID: <200304101627.43804.barwick@gmx.net>
Permalink:  ../200304101627.43804.barwick@gmx.net/
Also on:    postgresql.org/message-id/200304101627.43804.barwick@gmx.net

reply

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: barwick@gmx.net
  Subject: Re: INSERT INTO ... SELECT (PostgreSQL vs. MySQL)
  In-Reply-To: <200304101627.43804.barwick@gmx.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox