SQL Updatable View with joined tables

sql-server view sql-update sql-view
Red Taz picture Red Taz · Sep 2, 2011 · Viewed 32k times · Source

I have a view that looks similar to this,

SELECT  dbo.Staff.StaffId, dbo.Staff.StaffName, dbo.StaffPreferences.filter_type
FROM    dbo.Staff LEFT OUTER JOIN
        dbo.StaffPreferences ON dbo.Staff.StaffId = dbo.StaffPreferences.StaffId

I'm trying to update StaffPreferences.filter_type using,

UPDATE vw_Staff SET filter_type=1 WHERE StaffId=25

I have read this in an MSDN article,

Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

Does this mean that I can only update fields in dbo.Staff (which is all I can currently achieve) In this context does the definition of 'base table' not extend to any subsequently joined tables?

Answer

Mikael Eriksson picture Mikael Eriksson · Sep 2, 2011

Your statement should work just fine since you are only modifying column(s) from one table (StaffPreferences).

If you tried to update a columns from different tables in the same update statement you would get an error.

Msg 4405, Level 16, State 1, Line 7
View or function 'v_ViewName' is not updatable because the modification affects multiple base tables.

Related questions

Is there a way to retrieve the view definition from a SQL Server using plain ADO?

I'm successfully extracting column definitions from databases hosted on a SQL server using the ADO Connection OpenSchema() call in its various incarnations so I can programmatically recreate those tables in another SQL database. So far, so good. The main interaction …

Read More
How can I check if a View exists in a Database?

I have some SQL code that needs to be executed if a certain View exists in a database. How would I go about checking if the View exists? EDIT: The DBMS being used is Microsoft SQL Server

Read More
Create a view with ORDER BY clause

I'm trying to create a view with an ORDER BY clause. I have create it successfully on SQL Server 2012 SP1, but when I try to re-create it on SQL Server 2008 R2, I get this error: Msg 102, Level 15, State 1, Procedure TopUsers, …

Read More