🚨 MySQL Error: 'ERROR 1093 (HY000): You can't specify target table for update in FROM clause' — Cause, Fix, and Troubleshooting Guide

Overview

ERROR 1093 (HY000) is raised when an UPDATE or DELETE reads from the same table it is modifying inside a subquery in the FROM clause:

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

MySQL historically forbade referencing the mutation target within a subquery because it could not guarantee a stable snapshot of the table while writing to it. This is a query-shape restriction, not corruption — the fix is to restructure the statement.

Symptoms

• A DELETE ... WHERE id IN (SELECT ... FROM same_table ...) fails with 1093.
• An UPDATE t SET ... WHERE col = (SELECT MAX(col) FROM t) fails.
• The same logic runs fine in PostgreSQL or SQL Server but not MySQL 5.x.

Common Root Causes

1. Deleting rows selected from the same table

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

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

2. Updating based on an aggregate of the same table

UPDATE orders SET is_latest = 1
WHERE id = (SELECT MAX(id) FROM orders);

3. Correlated subquery referencing the target directly

Any inline SELECT ... FROM orders inside a statement that writes orders triggers it.

How to diagnose

The error message names the table verbatim. Read the statement and identify where the target table appears both as the mutation target and inside a FROM/IN (SELECT ...). No server-side diagnosis is needed — it is deterministic on statement shape.

Confirm your version, since MySQL 8.0.21+ relaxed some of these cases:

SELECT VERSION();

Fixes

Wrap the subquery in a derived table so MySQL materializes it first (the classic workaround):

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

The extra SELECT ... FROM (...) AS doomed forces MySQL to buffer the rows before the delete runs, side-stepping the restriction.

Rewrite an UPDATE as a self-JOIN, which is also usually faster:

UPDATE orders o
JOIN (SELECT MAX(id) AS max_id FROM orders) m ON o.id = m.max_id
SET o.is_latest = 1;

For a multi-row correlated update, join the derived aggregate:

UPDATE accounts a
JOIN (
  SELECT account_id, SUM(amount) AS total
  FROM transactions GROUP BY account_id
) t ON t.account_id = a.id
SET a.balance = t.total;

What to watch out for

• The derived-table trick works because MySQL materializes the inner result; do not add a second FROM orders at the outer level or you reintroduce the conflict.
• Self-JOIN rewrites can change row counts if the join is not unique — verify the join key is 1:1 before running against production.
• Wrap destructive rewrites in a transaction and test with SELECT first so a bad join does not delete the wrong rows.
• MySQL 8.0.21+ lifts some of these limits; relying on that behavior breaks on older or MariaDB targets.

Related

• ERROR 1064: SQL syntax error — the general parse error you may hit while restructuring the query.
• ERROR 1054: Unknown column — a frequent follow-on when aliasing derived tables incorrectly.
• ERROR 1146: Table doesn’t exist — check when a rewritten query references a missing alias or table.