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.

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

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.


Be Sociable, Share!