Overcoming the MySQL Error 1093 - Can’t Specify Target Table for Update

When working with MySQL, you may encounter the frustrating Error 1093: “You can’t specify target table for update in FROM clause.” This error typically arises when you’re trying to update or delete from a table while also selecting from it in a subquery. In this blog post, we will break down the problem and provide you with effective solutions to get your queries running smoothly again.

Understanding the Problem

In the scenario presented, the user has a table named story_category with corrupt entries. They attempted to delete these entries using a subquery in their DELETE statement:

DELETE FROM story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category 
    INNER JOIN story_category ON category_id=category.id);

Upon executing this query, they received the following error message:

#1093 - You can't specify target table 'story_category' for update in FROM clause 

This error occurs because MySQL does not allow you to modify a table (in this case, story_category) while simultaneously selecting from it. Let’s explore how to overcome this limitation.

Solution Strategies

1. Using Self-Join

One effective way to get around this error is by using a self-join. This method allows you to restructure your query to avoid using a subquery directly on the target table. Here’s how it works:

DELETE a
FROM story_category AS a
INNER JOIN category AS b 
ON a.category_id = b.id
WHERE b.id IS NULL;

In this example:

• story_category AS a denotes the main table we want to modify (or delete from).
• category AS b is the table we are joining with.
• By ensuring that only non-existent category IDs are matched, we clear the corrupt entries from story_category.

2. Nesting the Subquery

If you must use a subquery, consider nesting it deeper within the main query to create an implicit temporary table:

DELETE FROM story_category 
WHERE category_id NOT IN (
  SELECT * FROM (SELECT DISTINCT category.id 
  FROM category 
  INNER JOIN story_category ON category_id=category.id) AS temp);

This approach gets around the error by making MySQL treat the inner subquery as a temporary table, thus avoiding direct modification of the target table.

3. Adjusting the Query Optimizer

Starting from MySQL version 5.7.6, changes were made to the query optimizer which could still lead to this error despite using the nested subquery approach. If you encounter this issue, you might temporarily adjust the optimizer’s switches:

SET optimizer_switch = 'derived_merge=off';

This command tells MySQL not to merge derived tables, which might help in executing your queries. However, it’s critical to treat this as a short-term solution or for one-off tasks only, as it may impact query performance and results.

Conclusion

Encountering MySQL Error 1093 when trying to delete or update entries in a table can be a common hurdle, but there are effective strategies to navigate this issue. Whether you decide to join the table to itself, nest your subqueries, or adjust the optimizer settings, it’s important to choose a method that aligns with your database performance goals.

Feel free to explore these solutions and adapt them according to your specific database structure and needs. Happy querying!