[Solved] MySQL Error 1093: Can’t specify target table for update in FROM clause

Introduction

When working with MySQL, it’s not uncommon to encounter the error: Error 1093: Can't specify target table for update in FROM clause. This error can be perplexing, especially to those who are new to SQL. The error occurs because MySQL does not allow you to modify a table and select from the same table in a subquery within the same query.

Solution 1: Using a Temporary Table

This method involves creating a temporary table that stores the results you need. Since the temporary table is not the target table, it can be freely used for selections within the same query.

1. Create a temporary table and insert the desired records from the target table.
2. Update the target table by joining it with the temporary table.
3. Drop the temporary table if it’s no longer needed.

Example:

CREATE TEMPORARY TABLE temp_table AS
SELECT id FROM target_table WHERE condition;

UPDATE target_table
SET column_to_update = new_value
WHERE id IN (SELECT id FROM temp_table);

DROP TEMPORARY TABLE IF EXISTS temp_table;

Notes: This approach is straightforward but involves an extra step of creating a temporary table. It could consume more memory and time, especially with large datasets.

Solution 2: Using a Derived Table (Subquery in FROM Clause)

A derived table is a subquery in the FROM clause that MySQL executes and creates as a temporary table. This method allows you to circumvent the error by having the subquery execution separated from the update operation.

Example: Write a subquery to select the necessary records and use it as a derived table in the FROM clause of your UPDATE query:

UPDATE target_table
JOIN (
    SELECT id FROM target_table WHERE condition
) AS derived_table ON target_table.id = derived_table.id
SET target_table.column_to_update = new_value;

Notes: The derived table approach is useful when temporary tables are undesirable. However, there might be performance issues because it is essentially creating an implicit temporary table that could slow down the query for large datasets.

Solution 3: JOIN Operation

You can update a table using a JOIN operation that does not directly reference the same table in the subquery. This allows you to bypass the limitation,

Example: Use a JOIN operation with another table or an alias of the target table to reference the rows you want to update:

UPDATE target_table AS t1
JOIN (
    SELECT id FROM target_table WHERE condition
) AS t2
ON t1.id = t2.id
SET t1.column_to_update = new_value;

Notes: This solution may feel unintuitive but works well for sidestepping Error 1093. If joining with the same table, you must create an alias to reference it properly. Like the derived table, performance could be an issue when dealing with bigger tables.

Solution 4: Using FORCE INDEX

This advanced technique avoids Error 1093 by forcing MySQL’s optimizer to use an index when performing the update. It involves using the FORCE INDEX hint.

1. Identify the index that you want to force MySQL to use.
2. Write your update query using this index.

Example:

UPDATE target_table
FORCE INDEX (index_name)
SET column_to_update = new_value
WHERE id IN (
    SELECT id FROM target_table WHERE condition
);

Notes: This method is less straightforward and requires a good understanding of MySQL indexes. It can be useful in some scenarios to improve performance, but misuse might lead to worse performance or other unexpected results.