DELETE FROM t WHERE id IN (SELECT id FROM t WHERE condition) — the most common pattern, often copy-pasted from PostgreSQL where it is valid. MySQL sees both the outer DELETE target and the inner FROM t as the same table object and refuses to execute.. UPDATE t SET status = 'archived' WHERE id IN (SELECT id FROM t WHERE created_at < NOW() - INTERVAL 90 DAY) — self-referencing UPDATE in a WHERE subquery, identical problem to the DELETE variant but less frequently spotted during code review.. dbt post-hooks that implement a soft-delete or deduplication pattern: DELETE FROM {{ this }} WHERE id NOT IN (SELECT MAX(id) FROM {{ this }} GROUP BY natural_key). These hooks run after the model materializes and break because {{ this }} resolves to the same table on both sides.. ADF Script activities that were migrated from SQL Server or PostgreSQL without adaptation. Both those engines handle self-referencing DML differently, so the SQL passes peer review but fails at runtime on the MySQL target.. Stored procedures or event scheduler jobs that age out old records by selecting a batch of IDs from the same table: DELETE FROM events WHERE id IN (SELECT id FROM events WHERE ts < DATE_SUB(NOW(), INTERVAL 30 DAY) LIMIT 1000). The LIMIT inside the subquery adds extra complexity that MySQL also rejects.. ORM-generated queries from Django, SQLAlchemy, or similar frameworks when a bulk_delete() call builds a subquery filter against the same model — the ORM does not automatically add the derived-table wrapper that MySQL requires.. Airbyte or Fivetran normalization steps on MySQL destinations where the generated deduplication SQL was templated for a multi-engine target and does not include MySQL-specific workarounds.

Step 1 — Wrap the subquery in a derived table to force materialization before the DELETE executes: DELETE FROM t WHERE id IN (SELECT id FROM (SELECT id FROM t WHERE condition) AS tmp); The aliased subquery (tmp) makes MySQL treat the inner result as a temporary result set, breaking the self-reference.. Step 2 — Alternatively rewrite as a DELETE ... JOIN, which avoids the subquery entirely: DELETE t FROM t INNER JOIN (SELECT id FROM t WHERE condition) AS tmp ON t.id = tmp.id; This form is often faster on large tables because the optimizer can use an index on the join key.. Step 3 — For UPDATE statements apply the same derived-table pattern: UPDATE t SET col = value WHERE id IN (SELECT id FROM (SELECT id FROM t WHERE condition) AS tmp);. Step 4 — In dbt post-hooks replace the direct self-reference with a two-step approach: first insert the candidate IDs into a temporary table (CREATE TEMPORARY TABLE _tmp_ids AS SELECT id FROM {{ this }} WHERE condition;), then delete using that temp table (DELETE FROM {{ this }} WHERE id IN (SELECT id FROM _tmp_ids);), and finally drop it (DROP TEMPORARY TABLE _tmp_ids;). Wrap all three in a single post-hook list item using a semicolon-separated string or a macro.. Step 5 — In ADF Script activities, paste the rewritten query with the derived-table alias directly into the Script property of the activity. Validate by running the query manually against the MySQL target in MySQL Workbench or via a Lookup activity with a test row count before wiring it into the pipeline.. Step 6 — For ORM bulk deletes (Django / SQLAlchemy), bypass the ORM for this specific operation and execute the raw derived-table SQL via session.execute(text('...')) or connection.execute(). Document the workaround in a code comment so it is not reverted during a refactor.. Step 7 — If this pattern recurs across multiple pipelines, create a helper stored procedure that accepts the table name and condition as parameters and encapsulates the derived-table workaround. Call the procedure from dbt hooks or ADF instead of repeating the raw SQL.

Medium severityquery

MySQL Error:
1093

Q: Why does PostgreSQL allow this but MySQL throws error 1093?

PostgreSQL evaluates the FROM clause and creates an internal snapshot of the qualifying rows before it begins the modification pass. MySQL's DML executor does not implement that isolation step — it sees the target table being referenced for both reading and writing in the same statement scope and rejects it upfront with ER_UPDATE_TABLE_USED.

Q: Will adding a LIMIT or an index hint to the subquery fix MySQL error 1093?

No. The error is a parser-level restriction on the table reference, not a performance or locking issue. LIMIT, USE INDEX, and FORCE INDEX have no effect on whether the self-reference is allowed. The only fixes are the derived-table alias wrapper or rewriting as a JOIN-based DELETE.

Q: I'm getting 1093 inside a stored procedure that runs fine in development — why does it fail in the pipeline?

The error is deterministic regardless of environment. If it appeared to work in development, it was likely running against a different engine (MariaDB prior to 10.3, or a SQL Server/PostgreSQL test database). MariaDB handles some self-referencing patterns MySQL does not. Verify the target MySQL version and rewrite using the derived-table pattern.

Q: How do I rewrite this pattern in dbt without breaking the model's idempotency?

Use a post-hook macro that creates a TEMPORARY TABLE of the IDs to remove, performs the DELETE against that temp table, then drops it. Because TEMPORARY TABLE is session-scoped it does not persist between runs, preserving idempotency. Example: {% set cleanup %} CREATE TEMPORARY TABLE _del AS SELECT id FROM {{ this }} WHERE condition; DELETE FROM {{ this }} WHERE id IN (SELECT id FROM _del); DROP TEMPORARY TABLE _del; {% endset %} and call run_query(cleanup) in an on-run-end hook.

What does this error mean?

MySQL rejects any DELETE or UPDATE statement where the target table also appears in a subquery's FROM clause within the same statement. The engine cannot simultaneously read from and write to the same table in a single DML pass. In a data pipeline this surfaces when a dbt post-hook, an ADF Script activity, or a stored procedure tries to clean up rows by selecting candidate IDs from the same table it wants to modify. The statement fails immediately with ER_UPDATE_TABLE_USED and the pipeline step errors out — no rows are deleted or updated, and any downstream models that depend on the cleanup remain stale.

Common causes

1DELETE FROM t WHERE id IN (SELECT id FROM t WHERE condition) — the most common pattern, often copy-pasted from PostgreSQL where it is valid. MySQL sees both the outer DELETE target and the inner FROM t as the same table object and refuses to execute.
2UPDATE t SET status = 'archived' WHERE id IN (SELECT id FROM t WHERE created_at < NOW() - INTERVAL 90 DAY) — self-referencing UPDATE in a WHERE subquery, identical problem to the DELETE variant but less frequently spotted during code review.
3dbt post-hooks that implement a soft-delete or deduplication pattern: DELETE FROM {{ this }} WHERE id NOT IN (SELECT MAX(id) FROM {{ this }} GROUP BY natural_key). These hooks run after the model materializes and break because {{ this }} resolves to the same table on both sides.
4ADF Script activities that were migrated from SQL Server or PostgreSQL without adaptation. Both those engines handle self-referencing DML differently, so the SQL passes peer review but fails at runtime on the MySQL target.
5Stored procedures or event scheduler jobs that age out old records by selecting a batch of IDs from the same table: DELETE FROM events WHERE id IN (SELECT id FROM events WHERE ts < DATE_SUB(NOW(), INTERVAL 30 DAY) LIMIT 1000). The LIMIT inside the subquery adds extra complexity that MySQL also rejects.
6ORM-generated queries from Django, SQLAlchemy, or similar frameworks when a bulk_delete() call builds a subquery filter against the same model — the ORM does not automatically add the derived-table wrapper that MySQL requires.
7Airbyte or Fivetran normalization steps on MySQL destinations where the generated deduplication SQL was templated for a multi-engine target and does not include MySQL-specific workarounds.

How to fix it

1Step 1 — Wrap the subquery in a derived table to force materialization before the DELETE executes: DELETE FROM t WHERE id IN (SELECT id FROM (SELECT id FROM t WHERE condition) AS tmp); The aliased subquery (tmp) makes MySQL treat the inner result as a temporary result set, breaking the self-reference.
2Step 2 — Alternatively rewrite as a DELETE ... JOIN, which avoids the subquery entirely: DELETE t FROM t INNER JOIN (SELECT id FROM t WHERE condition) AS tmp ON t.id = tmp.id; This form is often faster on large tables because the optimizer can use an index on the join key.
3Step 3 — For UPDATE statements apply the same derived-table pattern: UPDATE t SET col = value WHERE id IN (SELECT id FROM (SELECT id FROM t WHERE condition) AS tmp);
4Step 4 — In dbt post-hooks replace the direct self-reference with a two-step approach: first insert the candidate IDs into a temporary table (CREATE TEMPORARY TABLE _tmp_ids AS SELECT id FROM {{ this }} WHERE condition;), then delete using that temp table (DELETE FROM {{ this }} WHERE id IN (SELECT id FROM _tmp_ids);), and finally drop it (DROP TEMPORARY TABLE _tmp_ids;). Wrap all three in a single post-hook list item using a semicolon-separated string or a macro.
5Step 5 — In ADF Script activities, paste the rewritten query with the derived-table alias directly into the Script property of the activity. Validate by running the query manually against the MySQL target in MySQL Workbench or via a Lookup activity with a test row count before wiring it into the pipeline.
6Step 6 — For ORM bulk deletes (Django / SQLAlchemy), bypass the ORM for this specific operation and execute the raw derived-table SQL via session.execute(text('...')) or connection.execute(). Document the workaround in a code comment so it is not reverted during a refactor.
7Step 7 — If this pattern recurs across multiple pipelines, create a helper stored procedure that accepts the table name and condition as parameters and encapsulates the derived-table workaround. Call the procedure from dbt hooks or ADF instead of repeating the raw SQL.

Example log output

ERROR 1093 (HY000): You can't specify target table 'events' for update in FROM clause
[dbt] Database Error in model post_hook (post-hook of model.my_project.events): (MySQLdb.OperationalError) (1093, "You can't specify target table 'events' for update in FROM clause")
[ADF] Activity 'CleanupExpiredRows' failed: MySQLException: Error Code: 1093. You can't specify target table 'staging_orders' for update in FROM clause

Frequently asked questions

Why does PostgreSQL allow this but MySQL throws error 1093?

PostgreSQL evaluates the FROM clause and creates an internal snapshot of the qualifying rows before it begins the modification pass. MySQL's DML executor does not implement that isolation step — it sees the target table being referenced for both reading and writing in the same statement scope and rejects it upfront with ER_UPDATE_TABLE_USED.

Will adding a LIMIT or an index hint to the subquery fix MySQL error 1093?

No. The error is a parser-level restriction on the table reference, not a performance or locking issue. LIMIT, USE INDEX, and FORCE INDEX have no effect on whether the self-reference is allowed. The only fixes are the derived-table alias wrapper or rewriting as a JOIN-based DELETE.

I'm getting 1093 inside a stored procedure that runs fine in development — why does it fail in the pipeline?