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 D5271B439C2
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  4 Jul 2003 21:46:10 +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 24612-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  4 Jul 2003 18:46:00 -0300 (ADT)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by svr1.postgresql.org (Postfix) with ESMTP id EBD34B439C1
	for <pgsql-sql@postgresql.org>; Fri,  4 Jul 2003 18:45:59 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h64LjkQQ008819;
	Fri, 4 Jul 2003 17:45:47 -0400 (EDT)
To: Evil Azrael <evilazrael@evilazrael.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: INSERT ... SELECT problem in Mysql 
In-reply-to: <158603745.20030704231102@evilazrael.de> 
References: <158603745.20030704231102@evilazrael.de>
Comments: In-reply-to Evil Azrael <evilazrael@evilazrael.de>
	message dated "Fri, 04 Jul 2003 23:11:02 +0200"
Date: Fri, 04 Jul 2003 17:45:46 -0400
Message-ID: <8818.1057355146@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Archive-Number: 200307/49
X-Sequence-Number: 14163

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.

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

         1) The following restrictions apply for Intermediate SQL:

            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>.

SQL99 puts it a little differently:

         Conformance Rules

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

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

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

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

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

			regards, tom lane