Can You Delete From A SQL Server View? – Yes and No Explained

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.

Rob StGeorge

Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply