MySQL 1093: Can't specify target table in FROM clause.
MySQL 1093: Can't specify target table in FROM clause.
When encountering the error 1093 in MySQL, indicating "Can't specify target table for update in FROM clause," the immediate action to take is to modify the query to avoid directly referencing the target table in a subquery within the FROM clause. Use a workaround by employing an intermediate subquery or a temporary table. Here are two approaches:
- Using an Intermediate Subquery:
If your original query looks something like this:DELETE FROM mytable WHERE id IN (SELECT id FROM mytable WHERE condition);
Modify it to use an intermediate subquery like so:DELETE FROM mytable WHERE id IN (SELECT id FROM (SELECT id FROM mytable WHERE condition) AS subquery);
- Using a Temporary Table:
Create a temporary table to store the intermediate result:CREATE TEMPORARY TABLE tempids AS SELECT id FROM mytable WHERE condition;
Then perform the operation using this temporary table:DELETE FROM mytable WHERE id IN (SELECT id FROM tempids);
After the operation, drop the temporary table if necessary:DROP TEMPORARY TABLE IF EXISTS temp_ids;
These actions circumvent the limitation by not directly referencing the target table in the FROM clause of the subquery.
Master
MySQL
debugging in Minutes
— Grab the Ultimate Cheatsheet
(Perfect for DevOps & SREs)
MySQL
Cheatsheet
(Perfect for DevOps & SREs)
MORE ISSUES
Backed by
certifed
certified