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