MySQL Bugs: #3575: Allow INSERT...SELECT MAX(x)+1 from the same table

Bug #3575	Allow INSERT...SELECT MAX(x)+1 from the same table
Submitted:	27 Apr 2004 7:20	Modified:	8 Nov 2004 10:36
Reporter:	Matthias Urlichs	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4.1	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
I am porting a legacy system which runs on a database that doesn't have auto-increment columns. Therefore they use INSERT...SELECT MAX(seqfield)+1.

MySQL can't do that: ERROR 1093 (HY000): You can't specify target table 'foo' for update in FROM clause.

I know that tere are several workarounds possible within MySQL (use a temp variable, use an autoincrement field, ...) but unfortunately I can't change the legacy system in a way that would be operationally incompatible with their current main database (mostly, but not restricted to, PostgreSQL).

How to repeat:
>create table foo(int lfd) type=innodb;

>insert into foo(lfd) values(123);

>insert into foo(lfd) values((select max(lfd) from foo)+1)
ERROR 1093 (HY000): You can't specify target table 'foo' for update in FROM clause

>insert into foo(lfd) values((select max(lfd)+1 from foo))
ERROR 1093 (HY000): You can't specify target table 'foo' for update in FROM clause

(I haven't checked other table types, as this system needs ref.integrity.)

Suggested fix:
If all the fields in question are aggregated (MAX,SUM,...) then there shouldn't be a problem and the INSERT should be able to proceed.

Hi,
First, this is documented in our manual (section "Subqueries errors"):
"* This error will occur in cases like this:
UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);
It's okay to use a subquery for assignment within an UPDATE statement, since subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table, in this case table t1, for both the subquery's FROM clause and the update target. ".
We have this limitation now, because such query was illegal in SQL-92; however as further SQL standards (SQL-99) now allow it we have it on our TODO.
Second, here is the simplest of workarounds: instead of using:
insert into foo(lfd) values((select max(lfd) from foo)+1)
just do
insert into foo(lfd) select (max(lfd)+1) from foo;

Sorry about not finding that limitation in the manual; the manual is big an I was of course searching for INSERT.*SELECT, not UPDATE. :-/

>Second, here is the simplest of workarounds: instead of using:
>insert into foo(lfd) values((select max(lfd) from foo)+1)
>just do
>insert into foo(lfd) select (max(lfd)+1) from foo;

Hmm. Good point, thanks.

Un-assigning it from me as I'm no specialist in this area.