How Can I Resolve MySQL Error 1093: Can't Specify Target Table for Update in FROM Clause?

MySQL Error 1093: Understanding the Restriction on Table Updates in FROM Clause

The MySQL error "You can't specify target table 'table_name' for update in FROM clause" arises when attempting to modify a table from within its own SELECT statement. This restriction stems from MySQL's inability to simultaneously update and reference a table in the same operation.

Addressing the Error

To overcome this issue, consider the following approaches:

• Joining the Table to Itself:
  Rearrange the query to perform a self-join on the table, creating a temporary table. This allows for updates on the original table:

UPDATE story_category a
INNER JOIN story_category b ON a.category_id = b.category_id
SET a.category_id = ...

• Nesting the Subquery Deeper:
  Nest the subquery that identifies the corrupt entries within another subquery, creating an implicit temporary table:

UPDATE story_category
SET category_id = (
  SELECT id
  FROM (
    SELECT id
    FROM category
    EXCEPT
    SELECT DISTINCT category_id
    FROM story_category
  ) AS x
)

• Disabling Query Optimizer:
  For MySQL versions 5.7.6 and onward, the optimizer may attempt to remove the subquery. To prevent this, disable optimizer_switch:

SET optimizer_switch = 'derived_merge=off';

Note: Disabling the query optimizer is not a recommended long-term solution and should only be used as a temporary fix. Consider the alternative approaches for more efficient database maintenance.

The above is the detailed content of How Can I Resolve MySQL Error 1093: Can't Specify Target Table for Update in FROM Clause?. For more information, please follow other related articles on the PHP Chinese website!