MySQL 1093: Can't specify target table in FROM clause.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
What is

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:

  1. 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);

  1. 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.

Attached error: 
MySQL 1093: Can't specify target table in FROM clause.
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

Master 

MySQL

 debugging in Minutes

— Grab the Ultimate Cheatsheet

(Perfect for DevOps & SREs)

Most-used commands
Real-world configs/examples
Handy troubleshooting shortcuts
Your email is safe with us. No spam, ever.

Thankyou for your submission

We have sent the cheatsheet on your email!
Oops! Something went wrong while submitting the form.

MySQL

Cheatsheet

(Perfect for DevOps & SREs)

Most-used commands
Your email is safe thing.

Thankyou for your submission

We have sent the cheatsheet on your email!
Oops! Something went wrong while submitting the form.

MORE ISSUES

Deep Sea Tech Inc. — Made with ❤️ in Bangalore & San Francisco 🏢

Doctor Droid