MySQL throws ER_CANT_REOPEN_TABLE (code 1137) when the same temporary table is referenced multiple times in a single statement without proper aliasing or materialization.
MySQL Error 1137: ER_CANT_REOPEN_TABLE indicates the server cannot reopen a temporary table referenced more than once in the same statement. Alias the table or rewrite the query so it is read only once to resolve the issue.
Can't reopen table: '%s'
Error 1137 appears with the message "Can't reopen table: '%s'" when MySQL fails to access a temporary table twice during one statement. The server closes the temporary table after its first read and cannot reopen it for the next reference, so execution stops.
The problem surfaces most often in complex SELECT statements that read from a derived or temporary table two or more times, such as self-joins, subqueries, UPDATE ...
JOIN constructs, or UNION chains.
MySQL internally materializes temporary tables to hold intermediary results. After reading that table once, the storage engine closes the handle. If the optimizer later asks for the same handle again, the server raises ER_CANT_REOPEN_TABLE because the table definition is no longer valid in the temporary context.
The limitation affects MySQL 5.0 through 8.0.
InnoDB, MyISAM, and MEMORY engines all observe the same rule because the error comes from the SQL layer, not the storage layer.
The error is common in UPDATE or DELETE statements that join a target table to a subquery built from the same table. It also appears in SELECT queries that self-join a derived table without assigning distinct aliases, or in CREATE TABLE ...
SELECT statements where the source subquery is reused in HAVING clauses.
Referencing one temporary or derived table more than once in the same execution scope triggers the error.
This includes reading the table in multiple JOINs, using it in both the SELECT list and WHERE clause, or joining the table to itself without aliases.
Using the same cursor name for two different derived tables can also confuse the optimizer and lead to the same error message.
Rewrite the query so each temporary or derived table is read exactly once.
The simplest approach is to assign a unique alias and materialize the result into a second temporary table if you must reference it again.
Alternatively, break a complex statement into two steps: first create a real temporary table with CREATE TEMPORARY TABLE ... SELECT, then run any subsequent SELECT, UPDATE, or DELETE against that table.
In a self-join on a derived table, use DISTINCT aliases for each appearance and ensure each alias points to a separate subquery.
For UPDATE joins, load the subquery into a standalone temporary table, index it, and join that table to the target.
When using UNION or GROUP BY, consider using WITH (Common Table Expressions) in MySQL 8.0+. CTEs are materialized only once and can be referenced multiple times without triggering ER_CANT_REOPEN_TABLE.
Always alias subqueries and derived tables, keep queries single-purpose, and materialize expensive subqueries into real TEMPORARY TABLE objects that can be safely reused.
Monitor the execution plan with EXPLAIN to confirm each derived table is accessed only once.
Galaxy’s context-aware SQL editor highlights repeated references to the same derived table and suggests automatic aliasing or CTE rewrites, preventing ER_CANT_REOPEN_TABLE before queries run.
ER_NONUNIQ_TABLE (1066) occurs when aliases collide. ER_BAD_FIELD_ERROR (1054) happens if a column in the derived table is misspelled after a rewrite. ER_TABLE_NOT_LOCKED_FOR_WRITE (1580) appears when a temporary table is updated without proper locking.
The fixes usually involve alias correction, column verification, or explicit LOCK TABLES.
.
A SELECT that joins a derived table to itself without separate subqueries forces MySQL to reopen the same temporary structure and fails.
The target table appears in the outer statement and inside the subquery, so the optimizer must read the temporary result twice.
Placing the same derived table logic in multiple clauses causes multiple reads of the temporary table.
Using a single alias for two different subqueries confuses the engine into treating them as one temporary table.
.
The storage engine closes temporary tables after the first read to conserve resources. Subsequent references lack a valid handle, triggering the error.
No. The derived_merge optimizer switch affects subquery merging but cannot reopen a closed temporary table.
Yes. The limitation is at the SQL layer, so MEMORY, InnoDB, and MyISAM all behave the same.
Galaxy’s AI copilot suggests CTE rewrites or temporary table materialization, letting you fix the error with one click before execution.
.avif)
.avif){kind=logo}
