How to Interpret and Fix the MySQL Error 1093 – Can’t specify target table for update in FROM clause

1. Overview

In this article, we’ll troubleshoot MySQL’s ERROR 1093 (HY000): You can’t specify target table ‘x’ for update in FROM clause. This error typically occurs when trying to access the outer query table within a subquery in an UPDATE or a DELETE statement.

In general, we can’t do this. MySQL puts this limitation in place to ensure data integrity and prevent potential conflicts that may arise from concurrent read and write operations on the same physical table within a single statement. If the table is being read and modified at the same time, the state of the data that is read could change during the UPDATE or DELETE part, potentially leading to inconsistent or incorrect results.

To demonstrate, we’ll use tables from Baeldung’s University database.

2. UPDATE Statement

Let’s imagine that we want to update the end_date in the Program table to equal the start_date for all rows. It makes sense that we might try:

UPDATE Program t 
SET t.end_date = (SELECT start_date FROM Program WHERE id = t.id);

However, MySQL tells us that it cannot do this:

ERROR 1093 (HY000): You can't specify target table 't' for update in FROM
clause

This is because we are asking to update Program while also selecting from it. Note that this error is not due to the subquery using a WHERE clause to match rows on the id column. We still get this error with a simpler query that attempts to update a table while selecting from it:

UPDATE Program 
SET end_date = (SELECT max(start_date) FROM Program);

What follows are a few ways that we can address ERROR 1093 (HY000) and still update our table like we want to.

2.1. Using a Self-Join

If our update involves related rows within the same table, for example, updating a column based on another row in the same table, we can often achieve this using a self-join:

UPDATE Program p1 
JOIN Program p2 
ON p1.id = p2.id 
SET p1.end_date = p2.start_date;

As noted in MySQL Select Query Optimization Using Self-Join: “By using self-join, you can avoid the need to create temporary tables or use subqueries, which can lead to slower query performance and higher resource utilization.”

However, a self-join is the preferred solution for some scenarios only, like deleting duplicates, matching rows, and other intra-table comparisons. It can’t be used, for example, if the subquery contains aggregate functions.

2.2. Using a Derived Table

We can use a derived table as the second option:

UPDATE Program
SET end_date = (
  SELECT max_start_date
  FROM (
        SELECT MAX(start_date) AS max_start_date 
        FROM Program
       ) AS derived_table
);

A derived table acts as an intermediate, discrete, materialized (or logically separated) result set. This works since MySQL’s optimizer doesn’t run the UPDATE until it has the result set from the derived table, addressing the concurrency issue.

This is preferred since it has the potential to perform better. For example, MySQL’s optimizer can often “merge” a derived table into the outer query plan, avoiding full materialization of the intermediate result set. Additionally, the optimizer can sometimes delay materializing a derived table until its result is needed or even avoid materialization entirely.

Also, it’s quite nice as there is no temporary table overhead as in our next solution.

2.3. Using a Temporary Table

Or, if our subquery is complex or involves a large dataset, we should first create a temporary table. Subsequently, we perform the UPDATE operation by joining our target table with the temporary table:

-- Create a temporary table 
CREATE TEMPORARY TABLE 
IF NOT EXISTS temp_table 
AS SELECT id, start_date 
FROM Program;

-- Update the main table using the temporary table
UPDATE Program t 
JOIN temp_table temp 
ON t.id = temp.id 
SET t.end_date = temp.start_date;

-- Drop the temporary table
DROP TEMPORARY TABLE 
IF EXISTS temp_table;

If we need to use the same intermediate result set multiple times, creating a temporary table can be more efficient. However, explicit CREATE TEMPORARY TABLE statements can sometimes cause issues with MySQL replication, particularly with statement-based replication, as they need to be logged and replayed correctly on the replica. Derived tables generally avoid these replication concerns as they are part of the query plan rather than separate DDL statements.

Also, note that we must remember to drop the temporary table afterward.

3. DELETE Statement

MySQL enforces this restriction with DELETE statements as well:

DELETE FROM Program 
WHERE start_date > 
(SELECT MIN(start_date) FROM Program);

This statement generates the same error message:

ERROR 1093 (HY000): You can't specify target table 'Program' for update in FROM clause

We can fix this error by using one of the same methods as discussed for UPDATEs.

3.1. Using a Self-Join

We can’t do a strict self-join with DELETEs. This is because we have an aggregate function MIN(start_date) in our subquery, and the self-join structure doesn’t provide the necessary materialization or isolation of that aggregate result. Therefore, we’ll still get ERROR 1093 with a simple self-join.

But, we can use a JOIN with a derived table:

DELETE p
FROM Program AS p
JOIN (SELECT MIN(start_date) AS min_start_date FROM Program) 
AS min_program_date
ON p.start_date > min_program_date.min_start_date;

In this example, we use DELETE alias FROM … JOIN … to explicitly tell MySQL which alias refers to the table we want to delete rows from. Let’s remember that when we declare an alias for a table, we should use the alias when referring to the table.

3.2. Using a Derived Table

Again, a derived table is our second choice:

DELETE FROM Program
WHERE start_date > (
  SELECT subquery_program.start_date
  FROM (
    SELECT MIN(start_date) AS start_date FROM Program
  ) AS subquery_program
);

3.3. Using a Temporary Table

A temporary table also works in the same way:

-- Create a temporary table 
CREATE TEMPORARY TABLE temp_min_start_date AS
SELECT MIN(start_date) AS min_date
FROM Program;

-- Delete from the main table using the temporary table
DELETE FROM Program
WHERE start_date > 
(SELECT min_date FROM temp_min_start_date);

-- Drop the temporary table
DROP TEMPORARY TABLE temp_min_start_date;

4. Conclusion

In this article, we learned about why ERROR 1093 happens and the different ways to fix it.

This error can occur in a few other scenarios that aren’t discussed as well – for example, when using the INSERT … ON DUPLICATE KEY UPDATE statement. So remember that, in general, we shouldn’t try to update data in a table using data selected from that same table.

There are often multiple alternatives; however, without running benchmark tests, we can’t be sure that a certain method or query is the best, so testing is paramount. A query that doesn’t require materialization of an intermediate result set is usually more efficient than one that does. A good rule of thumb is that we should prefer a query that merges (or “flattens”) derived tables used in the query, rather than materializing them.