If you want to delete from a SQL Server view there is one crucial factor that will determine whether or not it is possible.
Does the view involve more than one table?
- If it does, then you can’t delete from the view.
- If the view only refers to one underlying table then you can
Example of deleting from SQL view with a join to another table
USE DBAFire_DB CREATE TABLE MasterTable (id INT, subid INT, DateOfCreate DATE) CREATE TABLE subTable (id INT, information VARCHAR(25)) GO INSERT INTO MasterTable SELECT 1, 1, GETDATE() INSERT INTO MasterTable SELECT 2, 2, GETDATE() INSERT INTO subTable SELECT 1, 'First bit of info' INSERT INTO subTable SELECT 2, 'Second bit of info' --Now lets create a view of the sub information that includes the date from the master CREATE VIEW MultiTable AS SELECT st.*,DateOfCreate FROM subTable st join MasterTable mt ON st.id = mt.subid SELECT * FROM MultiTable DELETE FROM MultiTable WHERE id = 1
You will receive an error similar to:
Msg 4405, Level 16, State 1, Line 1
View or function ‘MultiTable’ is not updatable because the modification affects multiple base tables.
Now lets remove the join from the view and create a single table view.
CREATE VIEW SingleTable AS SELECT * FROM subTable DELETE FROM SingleTable WHERE id = 1
Success – (1 row(s) affected)
So as you can see yes it is possible to delete from views, however only if one underlying table is involved.