mySQL creating updatable view from two tables

Published 1 month ago by afabris

Ok, I want to create a view based on two user tables. View has to be updatable.

I have

CREATE OR REPLACE
ALGORITHM=UNDEFINED 
DEFINER=`root`@`localhost` SQL SECURITY DEFINER
VIEW `mantis_user_table` AS select 
        `front_myadmin`.`users`.`id` AS `id`,
        `front_myadmin`.`users`.`username` AS `username`,
        CONCAT(`front_myadmin`.`users`.`firstname`, ' ',  `front_myadmin`.`users`.`lastname`)  AS `realname`,
        `front_myadmin`.`users`.`email` AS `email`,
        `front_myadmin`.`users`.`password` AS `password`,
        `front_myadmin`.`users`.`enable` AS `enabled`,
        `front_myadmin`.`users`.`created_at` AS `date_created`,
        `front_mybugs`.`appsforce_user_table`.`protected` AS `protected`,
        `front_mybugs`.`appsforce_user_table`.`access_level` AS `access_level`,
        `front_mybugs`.`appsforce_user_table`.`login_count` AS `login_count`,
        `front_mybugs`.`appsforce_user_table`.`lost_password_request_count` AS `lost_password_request_count`,
        `front_mybugs`.`appsforce_user_table`.`failed_login_count` AS `failed_login_count`,
        `front_mybugs`.`appsforce_user_table`.`cookie_string` AS `cookie_string`,
        `front_mybugs`.`appsforce_user_table`.`last_visit` AS `last-visit` 
      
        from `front_myadmin`.`users` , `front_mybugs`.`appsforce_user_table` WHERE `front_myadmin`.`users`.`id` =  `front_mybugs`.`appsforce_user_table`.`id`;

But while this works and view is created as well as readable, I cannot update the underlying tables: Database query failed. Error received from database was #1393: Can not modify more than one base table through a join view 'front_mybugs.mantis_user_table' for the query: UPDATE mantis_user_table

I am far from SQL expert, and this is as far as my knowledge goes. At this link it seems that I have to use INNER JOIN (https://dev.mysql.com/doc/refman/5.5/en/view-updatability.html) but have no idea how to apply it to above code.

Any help really welcome at this stage!

Snapey
Snapey
1 month ago (683,015 XP)

i'm pretty sure that views are read only

afabris

@Snapey it says in the article (link above) that under certain conditions they are. At the bottom in the comments there is a example how to do this. However it is above my current knowledge.

Snapey
Snapey
1 month ago (683,015 XP)

Can you not just keep it simple and write to the two tables one at a time?

I see you have some logic in there for presentation only - not sure how a view is supposed to follow the reverse of this when storing the data?

However;

Also, only a single table in the view definition can be updated, so the SET clause must name only columns from one of the tables in the view.

This last point is going to make what you want to do impossible.

Just update them one by one.

afabris

@Snapey Here is the scenario maybe you have a better idea how to get around it. My Laravel App has user and roles database, and with it I can have a unknown number of other apps, lets say Mantis like in example above, WP, etc. That is all on the same server. I want to have seamless user integration in all apps. As each app has its own user authorization and user tables as it is independent system. Now the idea with views was to create a replacement user table which would get some columns from Laravel and some from the app I am trying to integrate. In this case I do not have to change every single query in every single app. Now while this successfully fools the app when reading the data, it does not work when it wants to update the data. The idea of syncing the user data is not good for this project, as it is unknown which apps will be there, and their potential structure of user and role data. I can get user data from APIs, and that works in some cases, where apps do not query user table too often and in too many places. The idea is to change the apps as little as possible, for stability and upgrade-ability. Also the solution should work for any app with minor modifications. I know there is no perfect solution, but any ideas how would you or anybody else approach this would be quite useful. Thanks in advance, Andre

Please sign in or create an account to participate in this conversation.