Using DELETE FROM t WHERE id IN (SELECT id FROM t WHERE condition) — MySQL cannot update/delete a table it is also reading in a subquery. UPDATE with a self-referencing subquery in the WHERE clause. dbt post-hooks that attempt to delete from a table while selecting from the same table

Step 1: Wrap the subquery in a derived table to break the self-reference: `DELETE FROM t WHERE id IN (SELECT id FROM (SELECT id FROM t WHERE condition) AS tmp);`. Step 2: Or use a JOIN for the delete: `DELETE t FROM t INNER JOIN (SELECT id FROM t WHERE condition) AS tmp ON t.id = tmp.id;`. Step 3: In dbt post-hooks, use a temporary table to stage the IDs to delete before the DELETE statement.

Medium severityquery

Power BI Refresh Error:
1093

What does this error mean?

A DELETE or UPDATE statement references the target table in a subquery's FROM clause, which MySQL does not allow.

Common causes

1Using DELETE FROM t WHERE id IN (SELECT id FROM t WHERE condition) — MySQL cannot update/delete a table it is also reading in a subquery
2UPDATE with a self-referencing subquery in the WHERE clause
3dbt post-hooks that attempt to delete from a table while selecting from the same table

How to fix it

1Step 1: Wrap the subquery in a derived table to break the self-reference: `DELETE FROM t WHERE id IN (SELECT id FROM (SELECT id FROM t WHERE condition) AS tmp);`
2Step 2: Or use a JOIN for the delete: `DELETE t FROM t INNER JOIN (SELECT id FROM t WHERE condition) AS tmp ON t.id = tmp.id;`
3Step 3: In dbt post-hooks, use a temporary table to stage the IDs to delete before the DELETE statement.

Frequently asked questions

Why does PostgreSQL allow this pattern but MySQL does not?

PostgreSQL evaluates the FROM clause independently and creates a snapshot of the data before modifying it. MySQL does not implement this isolation for DML statements, so it rejects self-referencing modifications.

What is the MySQL-idiomatic way to delete rows based on a condition on the same table?

Use a derived table alias: `DELETE FROM t WHERE id IN (SELECT id FROM (SELECT id FROM t WHERE status='deleted') AS tmp);` — the alias forces MySQL to materialize the subquery first.

Can MetricSign detect when this error breaks a dbt post-hook?

Yes — MetricSign captures dbt job failures and surfaces the run step error detail, making it easy to trace the failure to a specific post-hook SQL statement.

Official documentation: https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html