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

What it means

MySQL refuses an UPDATE or DELETE that changes a table and, in a subquery, reads from that same table: deleting duplicates by comparing with MIN(id), or setting a value from the table’s own MAX() or AVG(). The subquery would be reading rows while the statement changes them, which MySQL doesn’t allow, so it stops before starting. Nothing is changed.

“For update” here means “for changing”: DELETE gets the same message. The name in quotes is the table, or its alias if the statement gives it one.

MariaDB is different. From 10.3, MariaDB runs these statements by reading the subquery’s rows first, so the same SQL that fails on MySQL works there. Code that runs on MariaDB can therefore fail when moved to MySQL.

Common causes

1. Deleting duplicates with WHERE id NOT IN (SELECT MIN(id) FROM t GROUP BY …).
2. Updating from an aggregate of the same table: SET score = (SELECT MAX(score) FROM t) or WHERE score < (SELECT AVG(score) FROM t).
3. A multi-table DELETE or UPDATE whose WHERE has a subquery on one of the tables being changed.
4. Moving from MariaDB, or from PostgreSQL, both of which accept these statements.

How to fix it

Wrap the subquery in a derived table

Put the subquery inside another SELECT … FROM (…) AS alias. MySQL computes the inner result first, into a temporary table, so the statement no longer reads the table it’s changing:

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

The alias (AS keep) is required; without it you get “Every derived table must have its own alias”. In the test below this worked whether or not the inner query had a GROUP BY.

Or rewrite it as a join

A multi-table DELETE or UPDATE joins the table to itself (or to a derived table) and often reads more clearly:

-- keep the lowest id for each email, delete the rest
DELETE e
FROM emails e
JOIN emails keep ON keep.email = e.email AND keep.id < e.id;

-- reset scores below the average
UPDATE emails e
JOIN (SELECT AVG(score) AS a FROM emails) s
SET e.score = 0
WHERE e.score < s.a;

Run the matching SELECT first (SELECT e.* FROM emails e JOIN …) to see which rows will change.

Or do it in two steps

Read the value into a variable, or the ids into a temporary table, then change the rows:

SELECT MAX(score) INTO @top FROM emails;
UPDATE emails SET score = @top WHERE id = 1;

In a busy table, do both steps in one transaction and lock what you read (SELECT … FOR UPDATE) if the value mustn’t change in between.

Reproduce it

On MySQL 8.4.11, with a table emails (id, email, score) holding duplicate addresses:

DELETE FROM emails WHERE id NOT IN (SELECT MIN(id) FROM emails GROUP BY email);
UPDATE emails SET score = (SELECT MAX(score) FROM emails) WHERE id = 1;
UPDATE emails SET score = 0 WHERE score < (SELECT AVG(score) FROM emails);
DELETE e FROM emails e WHERE e.id IN (SELECT id FROM emails WHERE 1=0);

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

The derived-table DELETE above removed the three duplicates of six rows (ROW_COUNT() 3), and the self-join DELETE and the UPDATE … JOIN (SELECT AVG …) ran without error.

MariaDB 11.4.13 ran all four failing statements without an error, including the multi-table one: the first deleted the duplicates straight away.

In Inlet

On protected connections, Inlet’s query editor asks before running a DELETE or UPDATE without WHERE, and says why. When a statement fails, Inlet shows the error and links to this page; with your own Anthropic API key, Ask Claude (Pro, ⌘L) can rewrite it from the schema and the error.