From: Evil Azrael <evilazrael@evilazrael.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: INSERT ... SELECT problem in Mysql
Date: Sat, 5 Jul 2003 00:06:59 +0200
Message-ID: <798069325.20030705000659@evilazrael.de> (raw)
In-Reply-To: <8818.1057355146@sss.pgh.pa.us>
References: <158603745.20030704231102@evilazrael.de>
<8818.1057355146@sss.pgh.pa.us>
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
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: evilazrael@evilazrael.de, tgl@sss.pgh.pa.us
Subject: Re: INSERT ... SELECT problem in Mysql
In-Reply-To: <798069325.20030705000659@evilazrael.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox