📋

Description

This error indicates an attempt to update or delete data in more than one underlying table when using a view that is based on a JOIN operation. MariaDB, like MySQL, generally restricts DML operations on views to situations where only a single base table is affected by the modification. It occurs when an UPDATE or DELETE statement targets a join view and implicitly or explicitly tries to alter rows in multiple joined tables.

💬

Error Message

Can not modify more than one base table through a join view '%s.%s'

🔍

Known Causes

4 known causes

⚠️

Updating a Join View

The view being used for the DML statement is defined with a JOIN clause, linking two or more base tables. MariaDB's view updateability rules prevent modifications that affect multiple tables through such a view.

⚠️

Ambiguous DML Operation

The UPDATE or DELETE statement, while targeting a view, implicitly attempts to modify columns that belong to different underlying tables within that view's definition, making the operation ambiguous for MariaDB.

⚠️

Non-Updatable View Structure

The view's definition includes elements like DISTINCT, GROUP BY, HAVING, UNION, or subqueries in the SELECT list, which inherently make it non-updatable, especially when multiple tables are involved.

⚠️

Attempting Multi-Table Deletion/Update

An UPDATE or DELETE statement executed on a join view includes a WHERE or SET clause that implicitly targets records or columns from more than one of the underlying tables, leading to a disallowed multi-table modification attempt.

🛠️

Solutions

3 solutions available

1. Rewrite the View to Target a Single Table medium

Modify the view definition to only expose columns from one base table, allowing modifications.

1

Identify the base tables involved in the join view that you are trying to modify. The error message will specify the view name, and you can then examine its definition.

SHOW CREATE VIEW database_name.view_name;

2

Determine which of the base tables you intend to update. You can only update one table through a join view.

3

Rewrite the view definition to select columns only from the single target table you wish to modify. If you need to display data from multiple tables, create separate views for each modifiable table.

CREATE OR REPLACE VIEW database_name.view_name AS SELECT t1.column1, t1.column2 FROM table1 t1 JOIN table2 t2 ON t1.id = t2.fk_id;

4

Now, attempt to modify the data through the view, targeting the single exposed table.

UPDATE database_name.view_name SET column1 = 'new_value' WHERE id = 123;

2. Perform Direct Updates on Base Tables easy

Bypass the view and update the relevant base table directly.

1

Identify the specific base table that contains the data you need to modify. You can do this by examining the `SHOW CREATE VIEW` output.

SHOW CREATE VIEW database_name.view_name;

2

Construct and execute an `UPDATE` statement directly against that base table, using appropriate `WHERE` clauses to target the correct rows.

UPDATE table_name SET column_to_update = 'new_value' WHERE id = 123;

3. Use Stored Procedures for Complex Updates advanced

Encapsulate multi-table update logic within a stored procedure.

1

Create a stored procedure that contains the logic for updating multiple tables. This procedure will handle the transactional aspects and ensure data integrity.

DELIMITER //
CREATE PROCEDURE update_related_data(IN p_id INT, IN p_value1 VARCHAR(255), IN p_value2 INT)
BEGIN
  START TRANSACTION;
  UPDATE table1 SET column1 = p_value1 WHERE id = p_id;
  UPDATE table2 SET column2 = p_value2 WHERE fk_id = p_id;
  COMMIT;
END //
DELIMITER ;

2

Call the stored procedure to perform the desired updates.

CALL update_related_data(123, 'new_value_for_table1', 456);

🔗

Related Errors

5 related errors

MariaDB Error 1393: Cannot Modify Multiple Tables via View