Re: INSERT INTO ... SELECT (PostgreSQL vs. MySQL)

Ian Barwick wrote:
>
> I'm currently "porting" a smallish application from Postgres
> to MySQL [*]. I see that with MySQL it is not possible to perform
>
> INSERT INTO ... SELECT
>
> when the target table is the same as the source table, e.g.
>
> INSERT INTO foo (abc, xyz)
> SELECT abc, xyz FROM foo WHERE id = 1
>
> MySQL says: ERROR 1066: Not unique table/alias: 'foo'
>
> This statement works as expected in both PostgreSQL (at least 7.3.x)
> and also in Oracle 8i.

Microsoft SQL-Server 2000 has no problem with it either.

>
> The MySQL manual says:
>
> "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!)"

I didn't find anything like that in 15.8 of the SQL3 draft. Must be in
small print on the backside of the PostScript file I have. Sure, the
spec can be a bit confusing if one looks at it for the first time. I
didn't know that it confused the MySQL developer(s) that much.

For sure is it another perfect example why one has to be very carefull
when reading the MySQL documentation. Half of the reasons for why MySQL
differs from the standard or does not support basic standard
functionality are simply made up. The manual is full of lame excuses for
missing features and full of wrong or even dangerous advices or
workarounds every database professional can only shake his head over.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #