Sql Server: Execute delete against updateable view with joins

Question

In SQL it is possible to run inserts and updates against a view, as long as the view only selects data from one table. However, deletes don't seem to work quite so well. Can anyone help out?

Take this view for example:

CREATE VIEW v_MyUpdatableView
AS
    SELECT x.* FROM MyPrimaryTable x
    LEFT OUTER JOIN AnotherTable y ON y.MyPrimaryTableId = x.Id

I can run updates and inserts against this view and they happily pass through to MyPrimaryTable. However, if I run a delete I receive the following exception:

View or function 'v_MyUpdatableView' is not updatable because the modification affects multiple base tables.

Answer 1

I would just create a stored procedure that would delete the data from two tables. I know it's not pretty, but it would work or do logical deletes, where you update a column to be "deleted".

Answer 2

Quote:

DELETE statements remove data in one or more of the member tables through the partitioned view. The DELETE statements must adhere to this rule:

• DELETE statements are not allowed if there is a self-join with the same view, or any of the member tables.

Data Modification Rules - Creating a Partitioned View