A requirement just popped up for me, where I have to delete rows with duplicate "MainUrl" but these rows have unique ID, generated as an auto-increment column. Thought, it would be a straight-forward thing, but not until I actually sat down writing the query. Finally, after some lookup and some meditation, I ended up with the following query.
DELETE FROM CrawledUrl
WHERE ID NOT IN
GROUP BY MainUrl)
AND IsProcessed <> 'True'
ERROR 1093 (HY000): You can't specify target table 'CrawledUrl' for update in FROM clause
So after doing a little more meditation, I came to know that MySQL right now does not support both updates and select on the same table.
So, the fix? The problem, like I mentioned is to work on the same table which MySQL does not allow. So we need a temporary table - create one or let MySQL create one.
So when does MySQL create a temporary database? It does when you create an alias in the query saying (select * from myTable) as mT. Now mT is a temporary table and no longer the same myTable. So pretty neat huh!
Finally, the changed query was: