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

Overcoming MySQL Error 1093: "Can't Specify Target Table for Update in FROM Clause"

When encountering MySQL Error 1093, it's important to understand the underlying issue. This error occurs when attempting to modify a table that is also referenced in the FROM clause of a query.

Root Cause

MySQL does not allow tables to be simultaneously modified and used in the FROM clause of an UPDATE or DELETE statement. This restriction is due to the ambiguity that would arise from accessing the same table for both operations.

Solution 1: Using a Subquery

One workaround is to embed the SELECT statement that identifies the corrupt entries as a subquery in the FROM clause of the UPDATE or DELETE statement:

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

Solution 2: Self-Joining the Table

Alternatively, you can self-join the table to create separate aliases for the same table, allowing you to modify one of the aliases while referencing the other. For instance:

UPDATE story_category AS updated
INNER JOIN story_category AS original
    ON updated.category_id = original.category_id
SET updated.category_id = NULL
WHERE original.category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category 
      INNER JOIN story_category ON category_id=category.id
);

Avoiding a Reoccurrence

Once the corrupt entries have been deleted, consider implementing constraints on the story_category table to prevent future invalid entries:

ALTER TABLE story_category
ADD FOREIGN KEY (category_id) REFERENCES category(id);

The above is the detailed content of How to 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!