SQL Server: Doing a DELETE with a complex VIEW.

Question

I have a complex VIEW and would like to use the DELETE verb against it.

Can this be done?

Will the DELETE verb only affect the FROM table used in the SELECT statement?

I have about 8 joins. Will any of these joined tables be affected?

I am using SQL Server 2005.

Answer 1

The normal way of doing that would be:

delete from YourTable
where ID in (select ID from ComplexView)

Another option is to expand the view:

delete from a
from YourTable as a
join AnohterTable b ON a.bid = b.id
...

Using the double FROM syntax, you're always sure which table has the rows that will be deleted. See the MSDN page for DELETE for more examples.

Answer 2

The DELETE will only delete against one table in the view, with some conditions.

It's all contained here: Modifying Data Through a View

Answer 3

Deleting from a view is dangerous. In the first place you must be crystal clear what table is being deleted from (it wil lnot delete automatically from all tables). Next you may or may not have cascading deletes turned on, so you may or may not even be able to delete from the table you want to delete from as there are child records still in the other tables. It is best to do delete processes directly against the tables involved and to start with the child tables and work your way back to the parent table.

Answer 4

You can control precisely what is being deleted by using an INSTEAD OF DELETE trigger defined on the view. Inside the trigger you delete from the underlying table. But the user/application is exposed only to the view deletes from the 'view'.