📋

Description

This error occurs when an `UPDATE` statement attempts to modify a table that is also referenced in the `FROM` clause of a subquery used within the same `UPDATE` statement. MySQL prevents this direct self-referencing to avoid ambiguous or non-deterministic results during the update process.

💬

Error Message

You can't specify target table '%s' for update in FROM clause

🔍

Known Causes

3 known causes

⚠️

Direct Self-Referencing Update

An `UPDATE` query tries to modify a table that is simultaneously used in the `FROM` clause of a `SELECT` subquery within the `WHERE` or `SET` clause of the same update.

⚠️

Subquery Target Table Conflict

The table being updated is included in the `FROM` clause of an inner `SELECT` statement, leading to a conflict as MySQL cannot determine the table's state for both read and write operations.

⚠️

Nested Query Ambiguity

The database engine identifies an ambiguity when the target table for an update is also part of the data source for a subquery that helps define the update criteria.

🛠️

Solutions

4 solutions available

1. Use a Derived Table (Subquery in FROM Clause) easy

Wrap the subquery in another SELECT statement to treat it as a temporary table.

1

Identify the subquery that is causing the error. This is typically an UPDATE statement where the table being updated is also referenced in the FROM clause of a subquery.

2

Rewrite the query by enclosing the problematic subquery within another SELECT statement. This makes the subquery a 'derived table' or a temporary result set that the outer UPDATE can safely reference.

UPDATE your_table t1 SET t1.column_to_update = (SELECT sub_column FROM (SELECT sub_column FROM another_table WHERE condition) AS derived_table WHERE derived_table.some_id = t1.id LIMIT 1) WHERE EXISTS (SELECT 1 FROM another_table WHERE condition);

3

Alternatively, if the subquery is simpler and aims to find a single value to update, you might be able to achieve the same result with a JOIN.

2. Use a JOIN in the UPDATE Statement medium

Restructure the UPDATE to use a JOIN when updating based on conditions from another table.

1

Examine your UPDATE statement. If you are trying to update a table based on values or existence in another table, a JOIN is often the most efficient and direct solution.

2

Rewrite the UPDATE statement to include a JOIN clause that links the target table with the table(s) containing the conditions or values for the update. The `SET` clause can then directly reference columns from the joined tables.

UPDATE your_table t1 JOIN another_table t2 ON t1.join_column = t2.join_column SET t1.column_to_update = t2.source_column WHERE t2.condition_column = 'some_value';

3

Ensure the JOIN condition is correct and that you've specified the correct table aliases for clarity.

3. Use a Temporary Table medium

Store the results of the subquery in a temporary table and then update the main table using that temporary table.

1

Create a temporary table to hold the results of your subquery. This table will be automatically dropped when your session ends.

CREATE TEMPORARY TABLE temp_update_data AS SELECT sub_column, some_id FROM another_table WHERE condition;

2

Update your main table by joining it with the newly created temporary table. This avoids referencing the same table in the subquery and the target of the update.

UPDATE your_table t1 JOIN temp_update_data td ON t1.id = td.some_id SET t1.column_to_update = td.sub_column;

3

Optionally, you can add a WHERE clause to the UPDATE statement if you only want to update a subset of rows in your_table based on the temporary data.

4. Use EXISTS or IN for Existence Checks easy

For updates where the condition is simply about existence in another table, use EXISTS or IN.

1

If your subquery in the FROM clause is only being used to check for the existence of rows in another table to conditionally update the target table, rewrite it using `EXISTS` or `IN`.

UPDATE your_table t1 SET t1.column_to_update = 'new_value' WHERE EXISTS (SELECT 1 FROM another_table WHERE another_table.fk_id = t1.id AND another_table.status = 'active');

2

Alternatively, if you are selecting a list of IDs from the subquery to match against, use the `IN` operator.

UPDATE your_table t1 SET t1.column_to_update = 'new_value' WHERE t1.id IN (SELECT id FROM another_table WHERE status = 'completed');

🔗

Related Errors

5 related errors

MySQL Error 1093: Target Table in Subquery