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.
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'sforbidden in standard SQL to SELECT from the same table into which you are inserting. (The problem is that the
SELECTpossibly would find records that were inserted earlier during the same run. When using subquery clauses, the
situationcould easily be very confusing!)"
( http://www.mysql.com/doc/en/INSERT_SELECT.html )
Can anyone shed light on whether the above statement (especially
the bit about "standard SQL") is correct? I can't get my head
around MySQL being more standards compliant than Postgres here...
[*] I have probably committed some very heinous deed in a previous life ;-)
Ian Barwick
barwick@gmx.net