MySQL error 1093: Can't specify target table for update
An UPDATE or DELETE uses a subquery that reads the same table it is changing, which MySQL doesn't allow directly.
- Error number
1093- Name
ER_UPDATE_TABLE_USED- SQLSTATE
HY000- Category
- Syntax and statements
What it means
MySQL won't let a subquery in an UPDATE or DELETE read from the table being modified, for example DELETE FROM t WHERE id IN (SELECT id FROM t ...). The fix is to make MySQL materialize the subquery first, or to rewrite it as a join.
Common causes
- Deleting duplicates or old rows using a subquery on the same table.
- Updating rows based on an aggregate of the same table.
How to fix it
-
Rewrite as a multi-table
UPDATEorDELETEwith a join.DELETE t1 FROM users t1 JOIN users t2 ON t1.email = t2.email AND t1.id > t2.id;
-
Or wrap the subquery in a derived table so it is materialized first.
UPDATE products SET featured = 1 WHERE id IN (SELECT id FROM (SELECT id FROM products ORDER BY sales DESC LIMIT 10) AS top10);
Related errors
More syntax and statements errors
1064You have an error in your SQL syntax1149SQL syntax error1052Column is ambiguous1055Expression isn't in GROUP BY1136Column count doesn't match value count1248Every derived table must have its own alias1318Incorrect number of arguments
The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.
Work on MySQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.