How to Fix MySQL Error 1093: Can't Specify Target Table for UPDATE in FROM Clause

ERROR 1093 (HY000): You can't specify target table '<table>' for update in FROM clause is raised when an UPDATE or DELETE statement uses a subquery that reads from the same table being modified. The error symbol is ER_UPDATE_TABLE_USED.

Impact

MySQL prohibits reading from and writing to the same table within a single statement when the read happens inside a subquery in the FROM or WHERE clause. The statement is rejected entirely — no rows are modified, and the query returns an error immediately. This is not a data-consistency warning; the operation simply does not execute.

Developers most commonly encounter this when writing DELETE or UPDATE statements that filter rows based on aggregates or existence checks derived from the same table. ORMs that generate raw SQL (such as Django, SQLAlchemy, or ActiveRecord) can trigger this error when performing bulk updates or deletes with complex filter conditions that involve self-referencing subqueries.

Common Causes

Self-referencing subquery in WHERE — using a subquery against the target table to filter which rows to update or delete:

DELETE FROM orders
WHERE id IN (
  SELECT id FROM orders WHERE status = 'abandoned' AND created_at < NOW() - INTERVAL 30 DAY
);

Self-referencing subquery in SET — referencing the target table inside the SET clause of an UPDATE:

UPDATE products
SET price = price * 1.10
WHERE category_id IN (
  SELECT category_id FROM products WHERE price < 10
);

Correlated subqueries against the target table — using a correlated subquery that implicitly joins the target table to itself:

UPDATE employees
SET salary = salary * 1.05
WHERE department_id = (
  SELECT department_id FROM employees WHERE name = 'Alice'
);

Troubleshooting and Resolution Steps

Wrap the subquery in a derived table (most portable fix)

MySQL allows you to read from a subquery result that has been materialized as a derived table (an inner SELECT wrapped in another SELECT). Adding one extra level of nesting forces MySQL to evaluate the subquery into a temporary result before the outer write executes:

-- Original (fails with error 1093):
DELETE FROM orders
WHERE id IN (
  SELECT id FROM orders WHERE status = 'abandoned' AND created_at < NOW() - INTERVAL 30 DAY
);

-- Fixed: wrap in a derived table
DELETE FROM orders
WHERE id IN (
  SELECT id FROM (
    SELECT id FROM orders WHERE status = 'abandoned' AND created_at < NOW() - INTERVAL 30 DAY
  ) AS t
);

The same pattern applies to UPDATE:

-- Fixed UPDATE using a derived table
UPDATE products
SET price = price * 1.10
WHERE category_id IN (
  SELECT category_id FROM (
    SELECT DISTINCT category_id FROM products WHERE price < 10
  ) AS t
);

Rewrite using a JOIN

In many cases, replacing the subquery with a JOIN (for UPDATE) or a self-join (for DELETE) is cleaner and performs better because MySQL can optimize the join:

-- UPDATE with JOIN
UPDATE products p
JOIN (
  SELECT DISTINCT category_id FROM products WHERE price < 10
) AS cheap ON p.category_id = cheap.category_id
SET p.price = p.price * 1.10;

-- DELETE with JOIN
DELETE o FROM orders o
JOIN (
  SELECT id FROM orders WHERE status = 'abandoned' AND created_at < NOW() - INTERVAL 30 DAY
) AS stale ON o.id = stale.id;

Use a temporary table when working with large datasets

For large-scale operations, materializing the target row set into a temporary table first can improve both readability and performance:

CREATE TEMPORARY TABLE to_delete AS
  SELECT id FROM orders WHERE status = 'abandoned' AND created_at < NOW() - INTERVAL 30 DAY;

DELETE FROM orders WHERE id IN (SELECT id FROM to_delete);

DROP TEMPORARY TABLE to_delete;

Rewrite correlated subqueries as a self-join

-- Fixed correlated subquery using a JOIN
UPDATE employees e
JOIN employees ref ON ref.name = 'Alice'
SET e.salary = e.salary * 1.05
WHERE e.department_id = ref.department_id;

Additional Information

This restriction is specific to MySQL. PostgreSQL, SQL Server, and Oracle all permit reading from the target table in subqueries during UPDATE/DELETE operations. Code ported from those databases to MySQL will require this rewrite.
MySQL 8.0 did not remove this restriction. The derived-table workaround works on all widely used MySQL versions (5.6, 5.7, 8.0).
MariaDB (a MySQL fork) has the same restriction and the same derived-table workaround applies.
The SQLSTATE HY000 is a generic "general error" code. The specific nature of the error is fully described by the 1093 error number and message text.
If you see this error from an ORM, inspect the generated SQL using query logging (general_log = ON or the ORM's built-in echo/debug mode) to identify and rewrite the offending query.

Frequently Asked Questions

Why does wrapping the subquery in an extra SELECT fix the error?

MySQL's optimizer detects when a subquery references the same table being written to and rejects the query to avoid undefined behavior from reading rows that may be mid-update. When you add a wrapping SELECT, the inner result is materialized into a temporary derived table before the outer UPDATE or DELETE runs. MySQL no longer sees a direct reference to the target table in the subquery — it sees a reference to an anonymous derived table — so the restriction does not apply.

Does this error occur with multi-table DELETE syntax?

No. The multi-table DELETE syntax (DELETE t1 FROM t1 JOIN t2 ...) does not trigger error 1093 because the join is not a subquery. You can safely use multi-table delete or update syntax as an alternative to the derived-table workaround.

Will this error appear in transactions or only in single statements?

It is a statement-level error, not a transaction-level error. A single offending UPDATE or DELETE statement is rejected at parse/plan time. Other statements in the same transaction are unaffected, and no implicit rollback occurs.

Is there a MySQL configuration setting to disable this restriction?

No. This is a fundamental optimizer restriction, not a SQL mode option. Setting sql_mode has no effect on error 1093. The only fix is to rewrite the query using one of the patterns above.