pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedINSERT ... SELECT problem in Mysql
4+ messages / 3 participants
[nested] [flat]
* INSERT ... SELECT problem in Mysql
@ 2003-07-04 21:11 Evil Azrael <evilazrael@evilazrael.de>
0 siblings, 1 reply; 4+ messages in thread
From: Evil Azrael @ 2003-07-04 21:11 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: INSERT ... SELECT problem in Mysql
@ 2003-07-04 21:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Evil Azrael <evilazrael@evilazrael.de>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2003-07-04 21:45 UTC (permalink / raw)
To: Evil Azrael <evilazrael@evilazrael.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: INSERT ... SELECT problem in Mysql
@ 2003-07-04 22:06 Evil Azrael <evilazrael@evilazrael.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Evil Azrael @ 2003-07-04 22:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: INSERT ... SELECT problem in Mysql
@ 2003-07-05 12:14 Ian Barwick <barwick@gmx.net>
parent: Evil Azrael <evilazrael@evilazrael.de>
0 siblings, 0 replies; 4+ messages in thread
From: Ian Barwick @ 2003-07-05 12:14 UTC (permalink / raw)
To: Evil Azrael <evilazrael@evilazrael.de>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Saturday 05 July 2003 00:06, Evil Azrael wrote:
> 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 :)
I've been here before, this thread:
http://archives.postgresql.org/pgsql-sql/2003-04/msg00159.php
might be of interest.
Ian Barwick
barwick@gmx.net
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-07-05 12:14 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-04 21:11 INSERT ... SELECT problem in Mysql Evil Azrael <evilazrael@evilazrael.de>
2003-07-04 21:45 ` Re: INSERT ... SELECT problem in Mysql Tom Lane <tgl@sss.pgh.pa.us>
2003-07-04 22:06 ` Re: INSERT ... SELECT problem in Mysql Evil Azrael <evilazrael@evilazrael.de>
2003-07-05 12:14 ` Re: INSERT ... SELECT problem in Mysql Ian Barwick <barwick@gmx.net>
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