UPDATE or DELETE fails: can't specify target table in FROM clause
ERROR 1093 (HY000): You can't specify target table 'items' for update in FROM clauseShort answer
MySQL does not allow a subquery to read the same table that an UPDATE or DELETE is changing. Move the subquery into a derived table with an alias and join to it. Force it to materialize with a NO_MERGE hint, or the optimizer may merge it back.
Fix it fast
1Find out which cause you have
Find the table named after UPDATE or DELETE, then look for the same name inside a subquery. That is the clash.
EXPLAIN SELECT id FROM items WHERE retail / wholesale >= 1.3 AND quantity < 100;2Match what you see, then jump to its fix
What does the error look like?
ERROR 1093 (HY000): You can't specify target table 'items' for update in FROM clause
Cause 1 of 2
A subquery selects from the table being updated
1Check
Confirm that the subquery's table and the target table are the same.
SELECT id FROM items
WHERE retail / wholesale >= 1.3 AND quantity < 100;2Fix
Use a multi-table update. The second table is a derived table built from the first and referred to by its alias.
UPDATE items,
(SELECT id FROM items
WHERE retail / wholesale >= 1.3 AND quantity < 100) AS picked
SET items.retail = items.retail * 0.9
WHERE items.id = picked.id;3Verify
The statement reports the affected rows. Check one of them.
SELECT id, retail FROM items WHERE id = 1;Confirmed by dev.mysql.com
Cause 2 of 2
The optimizer merges the derived table back into the query
1Check
A merged derived table is read from the table directly again, which brings the clash back.
SELECT @@optimizer_switch\G2Fix
Force materialization for this one statement with the NO_MERGE hint, so no global setting changes.
UPDATE /*+ NO_MERGE(picked) */ items,
(SELECT id FROM items
WHERE retail / wholesale >= 1.3 AND quantity < 100) AS picked
SET items.retail = items.retail * 0.9
WHERE items.id = picked.id;3Verify
The statement runs without error 1093.
SELECT ROW_COUNT();Confirmed by dev.mysql.com
How do I stop it coming back?
- Preview with a SELECT using the same condition before running an UPDATE, so you know how many rows will change.
- Prefer a join against the derived table over IN (SELECT ...) on the same table.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix