Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

UPDATE or DELETE fails: can't specify target table in FROM clause

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

Short 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.

  • Applies to MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1UPDATE items ... WHERE id IN (SELECT id FROM items ...)A subquery selects from the table being updatedFix
    2. 2The rewrite still returns error 1093The optimizer merges the derived table back into the queryFix

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

Did this fix it?

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\G

2Fix

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.