Bug #39232 same-table subqueries don't work in DELETE statements
Submitted: 4 Sep 2008 5:43 Modified: 4 Sep 2008 7:02
Reporter: suresh jawaji Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:MySql 5.0.16 OS:Any
Assigned to: CPU Architecture:Any
Tags: delete, subqueries

[4 Sep 2008 5:43] suresh jawaji 
Description:
A subquery as part of a DELETE throws an error.

Error Code : 1093
You can't specify target table 'ADVANCED_REPORT_ATTRIBUTE' for update in FROM clause

How to repeat:
delete FROM ADVANCED_REPORT_ATTRIBUTE WHERE REPORT_ID IN(SELECT R.REPORT_ID   FROM ADVANCED_REPORT_ATTRIBUTE RATT, REPORT R, DATA_MODEL DM  WHERE DM.APPLICATION_ID = '76de482563b0e2017d00aa5c62c040a0.1' AND R.DATA_MODEL_ID = DM.DATA_MODEL_ID AND RATT.REPORT_ID = R.REPORT_ID)

Error Code : 1093
You can't specify target table 'ADVANCED_REPORT_ATTRIBUTE' for update in FROM clause

Suggested fix:
This is very much common sql and should be supported which is already suopported in other RDMS softwares.
[4 Sep 2008 7:02] Sveta Smirnova 
Thank you for the report.

This is expected behavior described at http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html So I only can verify this report as feature request.