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

The situation

You're updating rows based on a subquery that reads from the same table — deduplicating records, flagging rows based on an aggregation, cleaning up orphaned entries. The query looks completely reasonable. Then MySQL throws:

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

Here's a typical example — marking duplicate orders where the same customer_id appears more than once:

-- Mark orders as 'duplicate' if another order exists with the same customer_id
UPDATE orders
SET status = 'duplicate'
WHERE id NOT IN (
  SELECT MIN(id)
  FROM orders
  GROUP BY customer_id
);

MySQL refuses because you're targeting orders for UPDATE while the WHERE subquery also reads from orders. Reading and writing the same table in a single statement — not allowed. At least not directly.

Why MySQL does this

The problem is ambiguity. Should the subquery read the original rows, or rows already modified mid-update? MySQL can't answer that cleanly, so it blocks the statement entirely.

PostgreSQL and SQL Server sidestep this by materializing the subquery first — they snapshot the data before any writes happen. MySQL's optimizer takes a stricter stance and refuses the whole thing. The fix is to force that materialization yourself, by wrapping the subquery in a derived table.

Quick fix: wrap the subquery in another SELECT

Add one extra layer of SELECT around your inner query. MySQL treats the outer wrapper as a derived table — an anonymous temporary result — which breaks the direct reference cycle:

-- Wrap the subquery in an extra SELECT to create a derived table
UPDATE orders
SET status = 'duplicate'
WHERE id NOT IN (
  SELECT id FROM (
    SELECT MIN(id) AS id
    FROM orders
    GROUP BY customer_id
  ) AS tmp
);

Two things to note. First, the AS tmp alias is mandatory — MySQL rejects unnamed derived tables. Second, the inner query executes and materializes into tmp before the UPDATE runs, so there's no longer a conflict.

Better fix: rewrite using JOIN

JOIN-based updates are often cleaner and faster than nested subqueries, especially on tables with 10k+ rows. MySQL supports modifying rows through a JOIN directly:

-- Rewrite as a JOIN UPDATE
UPDATE orders o
JOIN (
  SELECT MIN(id) AS min_id, customer_id
  FROM orders
  GROUP BY customer_id
) AS keep_ids ON o.customer_id = keep_ids.customer_id
SET o.status = 'duplicate'
WHERE o.id != keep_ids.min_id;

Same logic, different structure. The derived table inside the JOIN computes independently before any rows get touched. No ambiguity. The query planner can also index-optimize a JOIN far more effectively than a correlated subquery.

Another common pattern: DELETE with the same error

ERROR 1093 hits DELETE just as hard:

-- This fails with the same error
DELETE FROM orders
WHERE id NOT IN (
  SELECT MIN(id) FROM orders GROUP BY customer_id
);

Same fix — add a wrapping SELECT:

DELETE FROM orders
WHERE id NOT IN (
  SELECT id FROM (
    SELECT MIN(id) AS id
    FROM orders
    GROUP BY customer_id
  ) AS tmp
);

MySQL 8.x: use a CTE instead

On MySQL 8.0+, CTEs are the cleanest option. MySQL 8 materializes a CTE as a separate step before executing the main statement — exactly what we need:

-- MySQL 8.0+ only
WITH keepers AS (
  SELECT MIN(id) AS id
  FROM orders
  GROUP BY customer_id
)
UPDATE orders
SET status = 'duplicate'
WHERE id NOT IN (SELECT id FROM keepers);

Far more readable than nested wrapping. The CTE runs once, produces a clean result set, and the UPDATE reads from that. No conflict, no workaround gymnastics.

Verify the fix before committing

Before running the UPDATE, swap it for a SELECT to preview which rows will be affected:

-- Preview which rows will be affected
SELECT id, customer_id, status
FROM orders
WHERE id NOT IN (
  SELECT id FROM (
    SELECT MIN(id) AS id
    FROM orders
    GROUP BY customer_id
  ) AS tmp
);

If the result set matches what you expect, your subquery logic is correct. Run the UPDATE, then confirm the affected row count looks right:

-- After the UPDATE
SELECT status, COUNT(*) FROM orders GROUP BY status;

Quick reference

• Root cause: MySQL blocks reading and writing the same table in one statement
• Fix 1: Wrap subquery — SELECT id FROM (...) AS tmp
• Fix 2: Rewrite as UPDATE ... JOIN (subquery) AS tmp — better for large tables
• Fix 3: MySQL 8.0+ — use a CTE for the cleanest syntax
• Always alias derived tables, or you'll get a separate "every derived table must have its own alias" error
• Test first with SELECT before running the actual UPDATE or DELETE