// MySQL error reference

MySQL error 1093: Can't specify target table for update

An UPDATE or DELETE uses a subquery that reads the same table it is changing, which MySQL doesn't allow directly.

Error number
1093
Name
ER_UPDATE_TABLE_USED
SQLSTATE
HY000
Category
Syntax and statements
Error message
You can't specify target table '%s' for update in FROM clause

What it means

MySQL won't let a subquery in an UPDATE or DELETE read from the table being modified, for example DELETE FROM t WHERE id IN (SELECT id FROM t ...). The fix is to make MySQL materialize the subquery first, or to rewrite it as a join.

Common causes

  • Deleting duplicates or old rows using a subquery on the same table.
  • Updating rows based on an aggregate of the same table.

How to fix it

  1. Rewrite as a multi-table UPDATE or DELETE with a join.

    DELETE t1 FROM users t1
    JOIN users t2 ON t1.email = t2.email AND t1.id > t2.id;
  2. Or wrap the subquery in a derived table so it is materialized first.

    UPDATE products
    SET featured = 1
    WHERE id IN (SELECT id FROM (SELECT id FROM products ORDER BY sales DESC LIMIT 10) AS top10);

Related errors

More syntax and statements errors

The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.

// fewer errors, faster fixes

Work on MySQL in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.