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:
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);
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.
(Perfect for DevOps & SREs)
(Perfect for DevOps & SREs)
