Date: Fri, 4 Jul 2003 23:11:02 +0200
From: Evil Azrael <evilazrael@evilazrael.de>
Message-ID: <158603745.20030704231102@evilazrael.de>
To: pgsql-sql@postgresql.org
Subject: INSERT ...  SELECT problem in Mysql
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

Hi!

I was trying to port a solution from postgresql to mysql for a mysql
user. im often using INSERT ... SELECTS with the same table in the
INSERT and SELECT statments at the same time. In Postgres i used to
use the destination only in a Subquery with IN, but in Mysql i had to
join the table, but it didn´t work and threw this error :  INSERT TABLE 'dummy' isn't allowed in FROM table list
with this page explaining the error :
http://www.mysql.de/doc/en/INSERT_SELECT.html

I have a problem with this part in the text
----
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!)
----

Is that correct? PostgreSQL allows this which is very handy, but
breaks so some ANSI compliance. Can anyform that ANSI SQL doesn´t
allow this?


Thanx

Christoph Nelles

  
-- 
Mit freundlichen Grüssen
Evil Azrael                          mailto:evilazrael@evilazrael.de