X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (developer.postgresql.org [64.117.224.193])
	by svr1.postgresql.org (Postfix) with ESMTP id 5D800B439CB
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  4 Jul 2003 22:07:21 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new,
	port 10024) with ESMTP id 29862-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  4 Jul 2003 19:07:10 -0300 (ADT)
Received: from mailout06.sul.t-online.com (mailout06.sul.t-online.com
	[194.25.134.19])
	by svr1.postgresql.org (Postfix) with ESMTP id 47FF9B439C6
	for <pgsql-sql@postgresql.org>; Fri,  4 Jul 2003 19:07:10 -0300 (ADT)
Received: from fwd07.aul.t-online.de by mailout06.sul.t-online.com with smtp
	id 19YYhu-0004B6-04; Sat, 05 Jul 2003 00:07:06 +0200
Received: from router.azrael.de
	(rITVvqZfweIypzS2fzODwl7rEhMHlpOXBw+MV+wxPV1dZzKAs0BFwk@[80.141.250.26])
	by fmrl07.sul.t-online.com
	with esmtp id 19YYhn-0CDqmu0; Sat, 5 Jul 2003 00:06:59 +0200
Received: from azrael.azrael.de (azrael.azrael.de [192.168.202.18])
	by router.azrael.de (8.11.4/8.11.4) with ESMTP id h64M6jG19327;
	Sat, 5 Jul 2003 00:06:50 +0200
Date: Sat, 5 Jul 2003 00:06:59 +0200
From: Evil Azrael <evilazrael@evilazrael.de>
X-Mailer: The Bat! (v1.62r) Personal
X-Priority: 3 (Normal)
Message-ID: <798069325.20030705000659@evilazrael.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: INSERT ... SELECT problem in Mysql
In-Reply-To: <8818.1057355146@sss.pgh.pa.us>
References: <158603745.20030704231102@evilazrael.de>
	<8818.1057355146@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: 8bit
X-Seen: false
X-ID: rITVvqZfweIypzS2fzODwl7rEhMHlpOXBw+MV+wxPV1dZzKAs0BFwk@t-dialin.net
X-Archive-Number: 200307/50
X-Sequence-Number: 14164

Thanx for the fast answer. I already thought something about like
this and considered sending the question to the advocacy list instead
of the SQL list. I really like their docs since i´ve read about their
reasons against Foreign Keys *g*

One more point in favor for PostgreSQL :)

Christoph Nelles


Am Freitag, 4. Juli 2003 um 23:45 schrieben Sie:

TL> Evil Azrael <evilazrael@evilazrael.de> writes:
>> 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.

TL> This is just MySQL's self-serving reading of the spec.  What SQL92
TL> actually says is (section 13.8)

TL>          1) The following restrictions apply for Intermediate SQL:

TL>             a) The leaf generally underlying table of T shall not be gen-
TL>               erally contained in the <query expression> immediately
TL>               contained in the <insert columns and source> except as the
TL>               <qualifier> of a <column reference>.

TL> SQL99 puts it a little differently:

TL>          Conformance Rules

TL>          1) Without Feature F781, "Self-referencing operations", no leaf
TL>             generally underlying table of T shall be generally contained in
TL>             the <query expression> immediately contained in the <insert
TL>             columns and source> except as the <table or query name> or
TL>             <correlation name> of a column reference.

TL> In other words, the behavior is perfectly standard, it's just not
TL> required for minimal SQL implementations to support it.

TL> What the standard behavior is is defined by this rule, earlier in the
TL> same section:

TL>          3) The <query expression> is effectively evaluated before inserting
TL>             any rows into B.

TL> that is, the SELECT doesn't see any rows that are being inserted into
TL> the target table during the same command.  There would not be any need
TL> for that rule if selecting from the same table you're inserting into
TL> were flat-out forbidden, as the MySQL docs claim.

TL>                         regards, tom lane



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