📋

Description

MariaDB Error 1093 occurs when an `UPDATE` statement attempts to modify a table that is also referenced in the `FROM` clause of a subquery within the same statement. This restriction prevents logical inconsistencies and unpredictable results that could arise from simultaneously reading and writing to the same data set.

💬

Error Message

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

🔍

Known Causes

3 known causes

⚠️

Self-Referencing Subquery

An `UPDATE` statement contains a subquery in its `SET` or `WHERE` clause that selects data directly from the table being updated.

⚠️

Subquery in Assignment

A column in the `SET` clause is assigned a value derived from a subquery that reads from the target table.

⚠️

Nested Subquery Conflict

A more complex query structure where a nested subquery, directly or indirectly, refers to the target table in its `FROM` clause.

🛠️

Solutions

3 solutions available

1. Use a Temporary Table easy

Isolate the data to be updated into a temporary table before performing the update.

1

Create a temporary table to hold the IDs or relevant data from the table you intend to update.

CREATE TEMPORARY TABLE temp_update_ids AS SELECT id FROM your_table WHERE condition_for_update;

2

Perform the update on the original table, referencing the temporary table.

UPDATE your_table SET column_to_update = new_value WHERE id IN (SELECT id FROM temp_update_ids);

3

The temporary table will be automatically dropped when the session ends.

2. Utilize a Subquery with JOIN medium

Rewrite the query to use a JOIN in the UPDATE statement, avoiding self-referencing the target table in the FROM clause.

1

Identify the table you are trying to update (target table) and the condition that determines which rows to update.

2

Rewrite the UPDATE statement to join the target table with itself or another derived table (subquery) to establish the conditions for the update.

UPDATE your_table t1 JOIN (SELECT id FROM your_table WHERE condition_for_update) t2 ON t1.id = t2.id SET t1.column_to_update = new_value;

3. Employ a Derived Table for the UPDATE Source medium

Create a derived table (subquery) that selects the necessary data and then update the original table based on that derived table.

1

Construct a subquery that selects the primary keys or unique identifiers of the rows you intend to update.

SELECT id FROM your_table WHERE condition_for_update

2

Use this subquery as a derived table in your UPDATE statement, joining it to the target table.

UPDATE your_table SET column_to_update = new_value WHERE id IN (SELECT id FROM (SELECT id FROM your_table WHERE condition_for_update) AS subquery_alias);

🔗

Related Errors

5 related errors

MariaDB Error 1093: Target Table Update Conflict