#1093 – You can’t specify target table ‘table_name’ for update in FROM clause

Have you ever got this MySQL error? Oh! You are searching for a solution right? You have come to the right place. Let me give you the simplest answer and it works charm.

For the time being assume that this is your query.

DELETE FROM table_name where coulmn_name IN (SELECT coulmn_name FROM table_name  WHERE coulmn_name > 10);

What you can do is change the query to something like this below.

DELETE FROM table_name where coulmn_name IN ( SELECT * FROM (SELECT coulmn_name FROM table_name  WHERE coulmn_name > 10) AS X) ;

Okay let me explain how the magic happens here.

You can not delete the rows from the same data source which your sub query refers to. Above mentioned query is a workaround, but it’s ugly for several reasons, including performance. Here nested sub query makes a temporary table. So it doesn’t count as the same table you’re trying to delete data from. In other words in MySQL, you can’t modify the same table which you use in the SELECT part. This behaviour is documented here.

Same theory applies for an UPDATE query too. I told you It’s easy. If you link to read more refer link1 and link2.

Below explanations are from stackoverflow.

This is because your update could be cyclical… what if updating that record causes something to happen which made the WHERE condition FALSE? You know that isn’t the case, but the engine doesn’t. There also could be opposing locks on the table in the operation.

Hope you got it right. Let me know in the comments section if you come across any issues.

MySQL

21 Comments

  1. legal jobs September 19, 2012 at 11:22 pm

    Great web site. Plenty of helpful information here. I’m sending it to a few friends ans additionally sharing in delicious. And obviously, thanks on your sweat!

    Reply
  2. affordable web designing services in dubai September 20, 2012 at 1:21 am

    I’ll right away take hold of your rss feed as I can not to find your email subscription hyperlink or e-newsletter service. Do you have any? Please let me know so that I may just subscribe. Thanks.

    Reply
  3. Very Funny September 20, 2012 at 5:59 am

    Unquestionably imagine that which you said. Your favorite reason seemed to be at the net the simplest thing to remember of. I say to you, I definitely get annoyed at the same time as folks think about concerns that they just don’t recognize about. You controlled to hit the nail upon the highest as smartly as outlined out the entire thing with no need side-effects , people can take a signal. Will probably be back to get more. Thanks

    Reply
  4. isolation par extérieure September 20, 2012 at 11:32 am

    Wonderful issues altogether, you simply gained a new reader. What might you recommend in regards to your submit that you simply made a few days ago? Any certain?

    Reply
  5. Gameserver September 21, 2012 at 11:38 pm

    Wonderful website. A lot of helpful info here. I’m sending it to several pals ans additionally sharing in delicious. And certainly, thanks to your sweat!

    Reply
  6. mike January 29, 2014 at 9:12 am

    It is truly a nice and helpful piece of info. I am happy that you shared this helpful info with us. Please stay us informed like this. Thanks for sharing.
    mike http://www.net-ict.be/

    Reply
  7. hellowangit August 18, 2014 at 6:10 am

    Thank you.It saved my time!

    Reply
  8. Martin September 5, 2014 at 8:53 am

    Thanks mate. Solved my problem.

    Reply
  9. FX Bayu Anggara June 10, 2015 at 5:50 am

    Thank you very much. Good explanation, and exactly solved my problems for doing an updates too. God bless you!

    Reply
  10. shimo June 11, 2015 at 5:49 pm

    Yeah man thanks!

    Reply
  11. Ravindu November 16, 2015 at 12:45 pm

    Thank you very much buddy….. :*

    Reply
  12. Pingback: MySQL Error 1093 – Can’t specify target table for update in FROM clause – ASK AND ANSWER

  13. mbags March 3, 2016 at 3:59 pm

    Excellent! Thank you!

    Reply
  14. Rodrigo May 2, 2016 at 2:46 pm

    Perfect, thank you!

    Reply
  15. Sebastian Götsch July 20, 2016 at 4:29 pm

    excellent

    Reply
  16. ADRIAN GIL October 8, 2017 at 3:17 am

    Thank you! you saved me

    Reply
  17. Gupta Anirudha November 27, 2017 at 5:38 am

    I am looking for solution from last 15 minutes and this post save my day. Thanks Dasun.

    Reply

Leave A Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.