I've created a view that INNER JOINS a bunch of tables but which would seem to still qualify to be updatable under MySQL rules, but updating data does not seem to work. Here is the view as shown in WorkBench:
CREATE ALGORITHM = UNDEFINED VIEW `event_registrants` AS SELECT `event_details`.`DTT_EVT_start` AS `Event_Start`, `attendee_meta`.`ATT_email` AS `ATT_email`, `users`.`ID` AS `ID`, `user_detail_tbl`.`first_name` AS `first_name`, `user_detail_tbl`.`nickname` AS `nickname`, `user_detail_tbl`.`last_name` AS `last_name`, `registration`.`EVT_ID` AS `EVT_ID`, `posts`.`post_title` AS `post_title`, `misc_tbl`.`ANS_value` AS `ANS_value` FROM ((((((`misc_tbl` JOIN `registration` ON ((`misc_tbl`.`REG_ID` = `registration`.`REG_ID`))) JOIN `event_details` ON ((`registration`.`EVT_ID` = `event_details`.`EVT_ID`))) JOIN `posts` ON ((`posts`.`ID` = `registration`.`EVT_ID`))) JOIN `attendee_meta` ON ((`registration`.`ATT_ID` = `attendee_meta`.`ATT_ID`))) JOIN `users` ON ((`attendee_meta`.`ATT_email` = `users`.`user_email`))) JOIN `user_detail_tbl` ON ((`users`.`ID` = `user_detail_tbl`.`user_id`)))
According to mysqltutorial.org (http://www.mysqltutorial.org/create-sql-updatable-views.aspx), one can check whether their views are updatable using
SELECT table_name, is_updatable FROM information_schema.views
The output from the query indicates that my view is updatable (is_updatable = YES)
For some reason a simple update like:
UPDATE event_registrants SET nickname='Terminator' WHERE first_name='John';
yields Error Code: 1288. The target table event registrants of the UPDATE is not updatable.
#mysql #sql