Home/Error Library/mysql-1093-target-table-update-from-clause

MySQL

MySQL 5.7–8.4 LTSQuery & Constraints

6 min read

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

MySQL Error 1093 — Why Can't I Query the Updated Table in a Subquery?

Architectural rationale behind Error 1093, derived table workarounds, optimizer merge pitfalls, MySQL 8 CTEs, and why physical temp table copying is harmful.

Reviewed by:Principal Database Reliability Architect

Tested Versions: MySQL 8.4 LTS, 8.0.40 & 5.7.44 (Docker 29.7)

Operational Verification & Reliability Metadata

Last Technical Review: 30 Ekim 2026

Tested VersionsMySQL 8.4 LTS, 8.0.40 & 5.7.44 (Debian Bookworm, Docker 29.7)

Required PrivilegesUPDATE privileges on target table and SELECT privileges in subquery

Risk Assessment

LowCreating redundant physical temp tables causes disk thrashing and prolonged table locks on large datasets

Expected OutputSuccessful query execution with 0 errors via derived table materialization, JOIN syntax, or CTE

Rollback Procedure:Rewrite UPDATE into JOIN or CTE syntax and execute within an explicit transaction

MySQL 8.4 Docs: UPDATE Statement Limitations

First 60 Seconds Emergency Protocol:

The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.

▸Symptom

plaintext

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

Typical SQL statement triggering this failure:

sql

UPDATE orders
SET status = 'cancelled'
WHERE id IN (
  SELECT id FROM orders WHERE total = 0 AND created_at < NOW() - INTERVAL 30 DAY
);

The query attempts to mutate a table (UPDATE) while concurrently referencing that same relation in a subquery filter (SELECT). While PostgreSQL, Oracle, and SQL Server permit this construct, MySQL aborts execution immediately with ERROR 1093.

▸Architectural Rationale Behind the Restriction

This restriction is a deliberate design choice protecting data consistency and replication integrity:

1Mid-Stream Result Mutation: MySQL processes subquery evaluation iteratively during the row update cycle. Reading from the target relation while updating it alters the qualifying row set mid-flight.
2Replication Indeterminism: Under Statement-Based Replication (SBR), evaluating mutating subqueries can result in different execution sequences on replicas, causing silent data divergence between Primary and Secondary nodes.

▸Three Verified Production Workarounds

1. Materialization via Derived Tables

Nesting the subquery within an additional SELECT layer forces MySQL to materialize the intermediate dataset into memory before initiating table mutations:

sql

UPDATE orders
SET status = 'cancelled'
WHERE id IN (
  SELECT id FROM (
    SELECT id FROM orders WHERE total = 0 AND created_at < NOW() - INTERVAL 30 DAY
  ) AS target_subset
);

WARNING

MySQL 8.0 Derived Merge Trap: The MySQL 8.0 query optimizer may attempt to flatten (merge) derived tables into the outer query, re-triggering Error 1093. In MySQL 8.0.22+, enforce materialization using the /*+ NO_MERGE(target_subset) */ optimizer hint: ``

sql
UPDATE orders SET status = 'cancelled'
WHERE id IN (
  SELECT /*+ NO_MERGE(target_subset) */ id FROM (
    SELECT id FROM orders WHERE total = 0
  ) AS target_subset
);

2. Multi-Table JOIN Syntax (Recommended Best Practice)

The multi-table UPDATE ... JOIN construct is cleaner, yields deterministic execution plans, and avoids subquery nesting complexity:

sql

UPDATE orders o
JOIN (
  SELECT id FROM orders WHERE total = 0 AND created_at < NOW() - INTERVAL 30 DAY
) sub ON sub.id = o.id
SET o.status = 'cancelled';

3. Common Table Expressions (MySQL 8.0+ CTE)

For modern MySQL 8.0 deployments, CTE syntax offers superior readability and maintainability:

sql

WITH target_records AS (
  SELECT id FROM orders WHERE total = 0 AND created_at < NOW() - INTERVAL 30 DAY
)
UPDATE orders o
JOIN target_records tr ON tr.id = o.id
SET o.status = 'cancelled';

▸Harmful Anti-Pattern: Physical Temporary Table Copies

A common pitfall is creating temporary physical tables to bypass the limitation. This is detrimental on production workloads:

•Incurs heavy disk I/O and buffer pool churn.
•Prolongs table lock duration during the copying phase.
•Introduces race conditions: Rows modified between the snapshot and the update create phantom inconsistencies.

▸Behavior Variations Across DML Constructs

•`DELETE` Operations: DELETE FROM orders WHERE id IN (SELECT id FROM orders ...) triggers the exact same 1093 error; apply the multi-table DELETE ... JOIN pattern to resolve it.
•`INSERT INTO ... SELECT`: MySQL explicitly permits reading from and writing to the same table in INSERT operations without throwing 1093.
•Scalar Subquery Updates: UPDATE orders SET total = (SELECT MAX(total) FROM orders) also encounters Error 1093.

▸Related Error Guides & Hub Links

•MySQL Error 1451 Cannot Delete Parent Row (H-38) — Foreign key delete constraints
•MySQL Error 1452 Foreign Key Constraint (H-14) — Missing parent records
•MySQL Deadlock Found 1213 (H-05) — Concurrent transaction lock resolution
•MySQL Hub — MySQL performance tuning and architecture

Other Database Error Triage Playbooks

Other battle-tested diagnostic guides for high-severity database errors.

All Errors (50)

MYSQL4 min