MySQL throws error 1093 when a statement reads from and writes to the same table in a single operation.
MySQL Error 1093: ER_UPDATE_TABLE_USED means the query tries to read from and update the same table simultaneously. Use a derived table, temporary table, or different alias to separate the read and write targets and the error disappears.
You can't specify target table '%s' for update in FROM
Error 1093 appears when a SQL statement selects from a table and updates or deletes from that same table in a single execution. MySQL blocks this pattern to avoid unpredictable results caused by reading rows that are being modified.
The restriction applies even when the read happens inside a subquery or derived table.
Unless MySQL can materialize that subquery into a separate temporary table, it treats both references as the same physical object and returns ER_UPDATE_TABLE_USED.
The most common trigger is an UPDATE or DELETE that contains a subquery in the WHERE clause referencing the same table.
The server detects the conflict during query planning and halts execution.
The error can also surface when using JOINs where one table alias points to the target table of the UPDATE or DELETE, or when a view that ultimately references the same table is involved.
Rewrite the query so the read operation happens on a separate object.
The simplest approach is to wrap the SELECT in an inline view and force materialization with optimizer_switch = 'derived_merge=off', use a temporary table, or self-join with a distinct alias when legal.
After isolating the read path, rerun the statement. The server now sees two independent tables and completes the update or delete without complaint.
Scenario: Removing duplicate rows.
Solution: Insert duplicate keys into a temporary table and delete from the original using that list.
Scenario: Updating rows based on an aggregate of the same table. Solution: Aggregate into a derived table first, then join it back to execute the update.
Always separate read and write targets in a single statement.
Use derived tables or temporary tables when data must come from the same source table.
Test complex DELETE or UPDATE statements in Galaxy’s modern SQL editor to catch ER_UPDATE_TABLE_USED during development, not production.
MySQL Error 1052 (column ambiguously defined) arises in self-joins when aliases are missing. Add explicit table aliases to avoid mis-references.
Error 1146 (table doesn’t exist) may appear after creating a temporary table in a different session.
Ensure the session scope matches the statement that references the table.
.
The WHERE clause selects from the target table inside a subquery, prompting MySQL to block the operation.
A self-join mistakenly reuses the original table alias for both reading and writing, causing a conflict.
MySQL merges a derived table or view back into the outer query, eliminating the separation needed to perform the update safely.
With optimizer_switch derived_merge on, the planner collapses the subquery into the main query and revives the conflict.
.
No. The restriction is hard-coded. You must rewrite the query or force materialization of a derived table.
Yes if the UPDATE part references the same source table. Use a temporary table or derived table to separate them.
No. MySQL 5.7, 8.0 and 8.1 still enforce the rule. The recommended workaround remains the same.
Galaxy’s SQL editor highlights table alias conflicts in real time and lets you run snippets separately, making it easy to materialize subqueries or temp tables before the final update.
.avif)
.avif){kind=logo}
