MySQL-cannot update view even though it shows as updatable

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

3 Likes37.85 GEEK