1

This problem may be limited to just some versions of Mysql.

From the console I know the view is updatable:

   mysql> select IS_UPDATABLE from INFORMATION_SCHEMA.VIEWS where TABLE_NAME = 'view_company_users';
   +--------------+
   | IS_UPDATABLE |
   +--------------+
   | YES          |
   +--------------+
   1 row in set (0.00 sec)


   mysql> UPDATE view_company_users SET company_public_phone = '0407111124' WHERE id=511;
   ERROR 1288 (HY000): The target table view_company_users of the UPDATE is not updatable

I imported the database from another server where this is not a problem, so it must be peculiar to my server settings (5.7.21-0ubuntu0.16.04.1)

This is the latest stable mysql package for ubuntu 16.04 at time of posting.

Can anyone advise me of what the problem might be, ie, why won't it allow me to update a row for a view that is meant to be updatable?

As requested.the view definition (with redactions):

    CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_company_users` AS select ....
    from ((((`admin_users` `au` 
    left join ... 
    left join ... 
    left join .....
    left join .....;
Gordon Rouse
  • 301
  • 3
  • 6
  • 2
    You should include the view definition, what you have shown us is not conclusive. – Tim Biegeleisen Feb 20 '18 at 06:46
  • Have confirmed "LEFT JOINS" make views non-updatable. When the view is re-initialised with 'INNER JOINS' the update statements do not throw errors. This however changes the nature of the view, so is a very poor workaround. – Gordon Rouse Feb 20 '18 at 10:06

1 Answers1

1

The solution here is to either use 'INNER JOINS' instead of 'LEFT JOINS', or to install MariaDB which seems to support this.

Gordon Rouse
  • 301
  • 3
  • 6