MySQL Bugs: #5037: DELETE FROM table WITH ( IN (SELECT blablalb FROM table))

Bug #5037	DELETE FROM table WITH ( IN (SELECT blablalb FROM table))
Submitted:	13 Aug 2004 20:49	Modified:	29 Mar 2011 18:26
Reporter:	Roberto Spadim (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.3-max, 4.1.3-standard	OS:	Linux (LINUX XP)
Assigned to:	Matthew Lord	CPU Architecture:	Any

Description:
delete isn't executed with subquery having same table name

You can't specify target table 'aviso_cobranca' for update in FROM clause

How to repeat:
delete from 
aviso_cobranca
where titulo_id IN 
(
	select titulo_id FROM 
		(
			select titulo_id,count(*) as quant from aviso_cobranca
			group by titulo_id
			having quant>1
		) AS tbl_1
)

Suggested fix:
?! i didn't understand why we can't do it

Dear Sir,

Thank you for your bug report!

This is expected behavior that is documented in the manual.  You can see a description of the 
specific problem here: http://dev.mysql.com/doc/mysql/en/Subquery_errors.html

If you have a support contract we can use that avenue to discuss any further.  You can also
use the community based channels:
mysql.com/IRC
lists.mysql.com

Best Regards

Quoting the manual:

Error 1093 (ER_UPDATE_TABLE_USED) SQLSTATE = HY000
Message = "You can't specify target table 'x' for update in FROM clause"

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.

I'd like to argue that this is a bug (although maybe we should call it a 'deficiency of feature'). Sure it's documented in the manual, and it looks like it's possible to do a join to get around it (which isn't mentioned above) but a sub-query should be able to access whatever it wants whereever it wants. It returns data that is then fed into the main query.

Even if you don't want to acknowledge this as a bug, please add it as a development goal (a todo) no matter how low the priority.

This works in MYSQL version 5.0.20a-standard

DELETE FROM TABLE1 WHERE TABLEID IN
   (
   select TABLEID from (SELECT TABLEID FROM TABLE1 WHERE TABLEID = 2689891) AS NEWTABLENAME
   );

ok done! :) i will close it