How Can I Fix MySQL Error 1093: 'Can't specify target table for update in FROM clause'?

Overcoming MySQL Error 1093: Updating Tables Specified in the FROM Clause

MySQL Error 1093 arises when attempting to update a table that's also used in the FROM clause of the same query. To resolve this error, consider the following approaches:

Joining the Table to Itself

If feasible, join the table to itself using appropriate selection criteria. This enables MySQL to view the table as separate entities and allows for destructive modifications:

UPDATE tbl AS a
INNER JOIN tbl AS b ON ....
SET a.col = b.col

Nesting the Subquery Deeper in the FROM Clause

As an alternative, nest the subquery deeper into the FROM clause to create an implicit temporary table:

UPDATE tbl SET col = (
  SELECT ... FROM (SELECT.... FROM) AS x);

However, this method is less efficient and may not be optimized properly by MySQL's query optimizer in certain versions.

Disabling Query Optimizer

In MySQL versions 5.7.6 and higher, the optimizer may prevent the subquery strategy. To disable optimization temporarily, set the optimizer_switch variable:

SET optimizer_switch = 'derived_merge=off';

Example Deletion Query

To address the specific query provided in the question, join the story_category table to the category table to identify and delete the corrupt entries:

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

In conclusion, by employing these techniques, you can overcome the "Can't specify target table for update in FROM clause" error and perform necessary modifications to your database tables.

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