Mysql: the definition of table prevents operation … on table

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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