#752 Can't edit updatable views in Adminer

[Lemon Juice]

Adminer will not allow me to edit views with results from multiple tables like this:

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `product_versions_edit` as
SELECT
  pv.prod_id
, pv.version_id
, p.name AS product_name
, pv.name

FROM product_versions pv

INNER JOIN products p
ON p.id=pv.prod_id;

This view can be updated in MySQL provided we only update values from a single table in the UPDATE statement. So in this case I can update version_id and name columns in one statement but I not product_name and name (because they come from different tables). And this is where Adminer causes trouble:

1. When editing a record in a separate page the UPDATE sql contains all fields from the table, which obviously triggers an sql error because MySQL treats it as an attempt to update 2 tables even if the values are unchanged. Solution: Adminer could detect if a value in the form has been changed and include only changed fields in the UPDATE statement.

2. When editing a record in the table listing (by using Ctrl+click on a cell) the update statement is almost fine (because it contains only the edited column) but it ends with LIMIT 1 (because it can't find a primary key in the view so it uses all columns in the WHERE clause). MySQL does not allow LIMIT in view updates. Solution: remove the LIMIT 1 clause from the update statement.

This problem doesn't exist in phpMyAdmin nor in DBeaver, where I can edit such views without problems. Would you be able to do something about it? This behaviour seems to be hardcoded so I can't find a way to change it with a plugin.

[James]

I'd like to second this request. Consider the situation where a table contains information about a customer's order, to show the customer name from a joined table with customers would be very useful however, as soon as I join a second table - the view immediatley becomes uneditable - regardless of whether I can edit it directly with sql.
I'd suggest perhaps allowing a checkbox next to the fields that you want to edit, so that when you click modify you can change that one field - this will mean your 'set' command will only be updating the fields you select and it is up to the user to select fields that are not in from both tables.

[Jakub Vrána]

I'd suggest using inline edit in table select (Ctrl+click). You can also select the row, use the button Edit at bottom and choose “original” for the fields you don't wish to edit.

I want the values in the form to be always stored as displayed regardless what was they originally (they can change in the background after the form was displayed). I also don't want to select the row again before saving it because it will slow down normal edits.