This is a tricky one affecting MySql table updates which have views. Documentation about this error actually explains the cause, but doesn’t connect the dots.
I have a view V on table T and want to delete some rows from T. That works fine as long as the DELETE statement doesn’t involve the view, so this works:
DELETE FROM T WHERE T.ID = ...;
But, if the DELETE statement involves the view, it won’t work:
DELETE FROM T WHERE T.ID IN
(
SELECT ...
FROM V
WHERE V.something = somethingelse
)
This results in an error “The definition of table V prevents operation delete on table T”.
If you come to think about it, it’s obvious that this would fail: I’m computing a condition which is affected by the delete operation. The same goes for UPDATE, by the way.
Dropping and re-creating the view doesn’t help because I need the view for the DELETE statement. I ended up SELECTing on V for the record IDs in T, writing them to a temporary table and using that temporary table to delete the specified records in T.
George's Techblog 