ERROR 1093 (HY000): Can't Specify Target Table for Update in FROM Clause in MySQL

Error Message#

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

What Triggers This Error#

MySQL 1093 fires when an UPDATE or DELETE statement references its target table through a subquery or derived table in the same statement — typically inside a FROM or IN (...) subquery. The restriction is specifically about this nested self-reference, not a blanket ban on reading from and writing to the same base table (multi-table UPDATE ... JOIN and DELETE ... JOIN forms on the same table are allowed). This differs from PostgreSQL, SQL Server, and the SQL standard, so developers migrating from other databases hit it immediately. The fix is always to break the self-reference, but the technique varies:

• DELETE ... WHERE id IN (SELECT ... FROM same_table) — classic deduplication pattern
• UPDATE ... SET col = (SELECT ... FROM same_table) — self-aggregate or self-lookup
• Correlated subquery on the same table — conditional update based on same-table data
• UPDATE with JOIN on a derived table that reads the same table
• Using a CTE (MySQL 8.0+) that references the target table — some CTE patterns still trigger 1093

Fix by Scenario#

DELETE with a subquery on the same table (deduplication)#

The most common case. You want to delete duplicate rows, keeping only the oldest (or newest) of each group.

-- This fails
DELETE FROM users
WHERE id NOT IN (
  SELECT MIN(id) FROM users GROUP BY email
);
-- ERROR 1093 (HY000): You can't specify target table 'users' for update in FROM clause

Fix — wrap the subquery in a derived table:

DELETE FROM users
WHERE id NOT IN (
  SELECT min_id FROM (
    SELECT MIN(id) AS min_id FROM users GROUP BY email
  ) AS keep
);

The outer SELECT over the derived table forces MySQL to materialize the subquery result before executing the DELETE, removing the read/write conflict on the same table.

Alternative — self-JOIN:

DELETE u1 FROM users u1
INNER JOIN users u2
  ON u1.email = u2.email
  AND u1.id > u2.id;

This deletes u1 rows whenever a u2 with the same email and a lower id exists — functionally equivalent to "keep the row with the smallest id per email group". Usually faster than the subquery approach on large tables.

UPDATE ... SET with a self-referential subquery#

Updating a column based on a value computed from the same table.

-- This fails
UPDATE products
SET price = price * 1.1
WHERE id IN (
  SELECT id FROM products WHERE category = 'electronics' AND stock < 10
);
-- ERROR 1093 (HY000)

Fix — derived table wrap:

UPDATE products
SET price = price * 1.1
WHERE id IN (
  SELECT id FROM (
    SELECT id FROM products WHERE category = 'electronics' AND stock < 10
  ) AS target
);

Alternative — combine conditions into a single WHERE:

UPDATE products
SET price = price * 1.1
WHERE category = 'electronics' AND stock < 10;

Whenever possible, prefer the direct WHERE — it's faster and avoids the 1093 issue entirely. The subquery form is only necessary when the filtering logic can't be expressed as simple column predicates (e.g., requires aggregation).

Self-aggregate — UPDATE based on MAX/MIN/COUNT#

Setting a value to the current max or a rank-based expression.

-- Give every user the highest balance in the system — FAILS
UPDATE accounts SET bonus = (
  SELECT MAX(balance) * 0.01 FROM accounts
);
-- ERROR 1093 (HY000)

Fix — compute the aggregate into a session variable first:

SELECT MAX(balance) * 0.01 INTO @max_bonus FROM accounts;
UPDATE accounts SET bonus = @max_bonus;

Or derived table:

UPDATE accounts
CROSS JOIN (SELECT MAX(balance) * 0.01 AS max_bonus FROM accounts) AS agg
SET accounts.bonus = agg.max_bonus;

The CROSS JOIN against a one-row derived table is a common MySQL idiom for "apply a scalar aggregate to every row".

Correlated subquery on the same table#

Updating rows based on values of other rows in the same table (e.g., copy from a parent row).

-- Copy parent's region to every child row — FAILS
UPDATE employees e1
SET region = (
  SELECT region FROM employees e2
  WHERE e2.id = e1.manager_id
);
-- ERROR 1093 (HY000)

Fix — use UPDATE ... JOIN:

UPDATE employees e1
JOIN employees e2 ON e2.id = e1.manager_id
SET e1.region = e2.region;

MySQL's multi-table UPDATE syntax handles the self-reference cleanly because the planner treats the two aliases as distinct row sources.

CTE referencing the target table (MySQL 8.0+)#

MySQL 8.0 added CTE support, but some CTE patterns still trigger 1093.

-- MySQL 8.0.13+: this works
WITH top_customers AS (
  SELECT customer_id FROM orders
  GROUP BY customer_id
  HAVING SUM(total) > 10000
)
UPDATE customers
SET vip = 1
WHERE id IN (SELECT customer_id FROM top_customers);
-- OK — the CTE doesn't reference customers

-- This fails (CTE references the target table)
WITH old_users AS (
  SELECT id FROM users WHERE last_login < '2024-01-01'
)
DELETE FROM users WHERE id IN (SELECT id FROM old_users);
-- ERROR 1093 (HY000) in some MySQL versions

Fix — add another derived table layer OR self-JOIN:

DELETE u FROM users u
INNER JOIN (
  SELECT id FROM users WHERE last_login < '2024-01-01'
) AS old ON u.id = old.id;

The extra layer of materialization reliably bypasses 1093 across MySQL 5.7, 8.0, and 8.4.

Prevention#

• When porting queries from Postgres or SQL Server, scan for UPDATE/DELETE statements with subqueries on the same table — these will fail on MySQL
• Prefer UPDATE ... JOIN or DELETE ... JOIN over subquery forms — they're usually faster and never trigger 1093
• For deduplication, keep a reusable self-JOIN template in your team's SQL snippets library
• Add the derived-table wrap as a last-resort pattern — simpler restructurings (direct WHERE, session variable, JOIN) usually work better
• Test multi-statement migrations against MySQL before deploying, even if they pass on other databases in CI
• If you use an ORM, know how it generates these statements — Django's .update() with a subquery can produce 1093-prone SQL