How to Solve MySQL Error 1093: 'You can't specify target table 'table_name' for update in FROM clause'?

MySQL Error 1093: Understanding and Overcoming the Restriction

In MySQL, attempts to modify a table that is also referenced in the FROM clause of the same query can result in the error: "You can't specify target table 'table_name' for update in FROM clause." This restriction arises from MySQL's inability to perform such operations directly.

To resolve this issue, several approaches can be considered:

Joining the Table to Itself

One option is to join the table to itself with appropriate selection criteria, creating two distinct instances of the table from MySQL's perspective. This allows destructive operations to be performed on one instance while leaving the other untouched.

Nesting Subqueries in FROM Clause

An alternative workaround involves nesting the subquery that generates the target rows for deletion within a deeper FROM clause. This creates an implicit temporary table from the subquery, which MySQL recognizes as a separate entity from the target table. However, this approach may impact performance.

Disabling Optimizer Optimization (MySQL 5.7.6 and later)

Starting with MySQL 5.7.6, the optimizer may optimize out the subquery in the FROM clause, causing the error to persist. To overcome this, disable the optimization using the optimizer_switch variable:

SET optimizer_switch = 'derived_merge=off';

Note that this is recommended as a short-term solution or for small one-off tasks, as it may negatively impact overall query performance.

Conclusion

The error "You can't specify target table 'table_name' for update in FROM clause" stems from MySQL's limitation in modifying a table that is also used in the FROM clause. By employing the methods outlined above, developers can overcome this restriction and perform the desired operations on their data.

The above is the detailed content of How to Solve MySQL Error 1093: 'You can't specify target table 'table_name' for update in FROM clause'?. For more information, please follow other related articles on the PHP Chinese website!