Bug #401 Referencing same table
Submitted: 8 May 2003 10:59 Modified: 22 May 2003 15:55
Reporter: Peter Deacon Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:4.1 OS:Windows (win32)
Assigned to: CPU Architecture:Any

[8 May 2003 10:59] Peter Deacon
Description:
There is a restriction on being able to insert into a table where the same table is referenced in the select query:

INSERT INTO mytable (fielda) 
SELECT 'myvalue' FROM mytable

There are obvious workarounds but it's a big deal for me and would very much appreciate this restriction going away :)

How to repeat:
[22 May 2003 15:55] MySQL Verification Team
This isn't a bug. Please read the Manual's item Insert Syntax:
The INSERT ... SELECT form inserts rows selected from another table or tables.
[22 May 2003 16:22] Peter Deacon
Can you name just one of the major database players who 'forbid' such a thing?  Adhereing to standards is always nice but your ignoring the reality of the history and usefulness of this.  Standards are not a replacement for experience...(And IMHO this very useful feature)

I need to point out this was submitted as a feature request *NOT* a bug.

The idea that changes would interfere is an implementation/complexity issue, there are no logical contridictions in supporting same table references between insert ... select
[23 May 2003 9:43] Peter Gulutzan
It is true that this feature now exists in other DBMSs, and is no longer 
forbidden in the newest version of the SQL standard. It is a valid feature 
request for the future, as it is MySQL's goal to support whatever is legal in 
the current standard. However, as the manual says, it would be difficult to 
implement because the SELECT could find rows that were just INSERTed during 
the same statement.