📋

Description

MySQL Error 1395 occurs when you attempt to execute a DELETE statement directly on a view that has been defined using a JOIN clause. MySQL does not permit direct deletion from such views because it cannot unambiguously determine which underlying base table's rows should be affected.

💬

Error Message

Can not delete from join view '%s.%s'

🔍

Known Causes

3 known causes

⚠️

Direct Deletion Attempt on Non-Updatable View

You are trying to use a DELETE statement directly on a MySQL view that is not inherently updatable, specifically one defined with a JOIN clause.

⚠️

View Definition Includes a JOIN Clause

The view you are attempting to delete from was created using a JOIN operation, which makes it non-updatable for DELETE operations in MySQL.

⚠️

Ambiguity in Underlying Table for Deletion

When a view combines data from multiple tables via a JOIN, MySQL cannot determine which specific base table row(s) should be deleted, leading to this error.

🛠️

Solutions

3 solutions available

1. Delete from Underlying Tables Directly easy

Bypass the view and delete rows from the actual tables involved in the join.

1

Identify the tables that form the join view. You can usually do this by examining the `CREATE VIEW` statement for the view in question.

SHOW CREATE VIEW your_database.your_view_name;

2

Determine which table contains the row(s) you want to delete. This often depends on the join type (e.g., INNER JOIN, LEFT JOIN) and the columns involved in the deletion condition.

3

Construct a `DELETE` statement targeting the specific table and row(s). Ensure your `WHERE` clause accurately identifies the row(s) to be removed.

DELETE FROM table_name WHERE condition_to_identify_row;

4

Execute the `DELETE` statement. If the deletion affects rows in multiple tables (due to foreign key cascading deletes), this single statement might be sufficient.

2. Rewrite the View to Allow Deletions medium

Modify the view definition to ensure it's updatable, if possible.

1

Examine the `CREATE VIEW` statement for the problematic view. Understand the tables and join conditions used.

SHOW CREATE VIEW your_database.your_view_name;

2

Identify the reasons why the view is not updatable. Common reasons include: using `GROUP BY`, `DISTINCT`, aggregate functions, subqueries in the `SELECT` list, or joins on non-key columns from multiple tables where the primary key is not clearly identifiable.

3

If the view involves a join between two tables and you want to delete from one of them, ensure the view references the primary key of the table you intend to delete from. For example, if deleting from `table_a` via a view joining `table_a` and `table_b`, the view's `SELECT` list should include the primary key of `table_a`.

4

Drop the existing view.

DROP VIEW your_database.your_view_name;

5

Recreate the view with a definition that allows deletions. This might involve simplifying the join or ensuring the primary key of the target table is present and unambiguous.

CREATE VIEW your_database.your_view_name AS SELECT ... FROM table_a JOIN table_b ON table_a.id = table_b.a_id WHERE ...;

6

Attempt to delete from the view again.

DELETE FROM your_database.your_view_name WHERE condition_on_view;

3. Use an `INSTEAD OF` Trigger (MySQL 8.0.14+) advanced

Implement a trigger to intercept DELETE operations on the view and perform the deletion on the underlying tables.

1

Understand the structure of your join view and the underlying tables. Determine which table(s) should be affected by a `DELETE` operation on the view.

SHOW CREATE VIEW your_database.your_view_name;

2

Create an `INSTEAD OF` trigger for the `DELETE` event on your view. This trigger will execute *instead* of the default `DELETE` action.

DELIMITER //
CREATE TRIGGER trigger_name
INSTEAD OF DELETE ON your_database.your_view_name
FOR EACH ROW
BEGIN
    -- Your deletion logic here
END //
DELIMITER ;

3

Inside the trigger, write `DELETE` statements targeting the appropriate underlying table(s). Use the `OLD` keyword to refer to the values of the row that would have been deleted from the view.

DELETE FROM table_name WHERE table_name.primary_key_column = OLD.primary_key_column_from_view;

4

If the deletion needs to occur across multiple tables (e.g., due to cascading deletes configured in your schema), ensure your trigger handles this. You might need multiple `DELETE` statements within the trigger.

5

Test the `DELETE` operation on the view to ensure the trigger functions as expected.

DELETE FROM your_database.your_view_name WHERE condition_on_view;

🔗

Related Errors

5 related errors

MySQL Error 1395: Cannot Delete from Join View