Bug #66455 Mysql error 1093 - Can't specify target table for update in FROM clause

• Submitted: 19 Aug 2012 8:41
• Modified: 19 Aug 2012 9:37
• Reporter: Konkov Eugen
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any

[19 Aug 2012 8:41] Konkov Eugen

Description:
delete from services 
where service_id in 

(select s1.service_id
from services s1
left join services s2 on s1.uplink_service_id = s2.service_id
where s1.uplink_service_id <> 0 and s2.service_id is null )

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

How to repeat:
You can do this as two stages:
1. Get result for subquery
2. Delete all rows from result

[19 Aug 2012 9:24] Valeriy Kravchuk

This is a documented limitation, see http://dev.mysql.com/doc/refman/5.5/en/subquery-errors.html or http://dev.mysql.com/doc/refman/5.5/en/delete.html:

"Currently, you cannot delete from a table and select from the same table in a subquery."

You can probably rewrite your query using multiple table delete syntax, like this:

mysql> delete tt1.* from t1 tt1 join t1 tt2 left join t1 tt3 on (tt2.id = tt3.id and tt3.id <>0 and tt3.id is not null) where tt1.id = tt2.id;
Query OK, 2 rows affected (0.34 sec)

[19 Aug 2012 9:37] Konkov Eugen

I know about that limitations, but rewriting is ugly
Do autorewrite by server

[22 Mar 2023 20:26] Mark Callaghan

Encountering this today. Fixing this in MySQL would save me and many other people much time.

If this is part of a SQL standard, and most other DBMS support it, then "won't fix" seems like a better status than "not a bug". Documenting it doesn't mean it isn't a bug, or missing feature.